﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using FW;
using dbiparser;
using System.Threading;
using System.Threading.Tasks;
using System.Drawing;
//using ExcelLibrary.SpreadSheet;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

namespace kserv
{
    public class consulta
    {
        kservDataContext db;

        private string crchave = "q1w2e3r4t5y6u7i8o9p0zaxscdvfbgnhmjkl";

        private fwlog log;
        private configSRV csrv;
        private string _msg;
        private string erro = "";
        private string uf = "";
        private List<int> servicosOS = new List<int>();
        private int _idemp;
        private long _idos;
        private string codigoint = "";
        private string empresaint = "";
        private string filialint = "";
        private string cidade = "";
        private int _idthread;
        private string acaoThread = "";
        private int waitThread = 0;
        public int IDTHREAD { get { return _idthread; } set { _idthread = value; } }
        private long _idexecucao;
        public long IDEXEC { get { return _idexecucao; } set { _idexecucao = value; } }
        public string ACAOTHREAD { get { return acaoThread; } }
        private Thread[] slots;


        public consulta(long IDOS, int IDEMP, int IDTH, long IDEX)
        {
            csrv = new configSRV();
            log = new fwlog(csrv.TEMPDIR + "\\log.dbiKcad.txt", csrv.TIPOLOG);
            _msg = "";
            db = new kservDataContext(csrv.DBINFECONNSTRING);

            _idemp = IDEMP;
            _idos = IDOS;
            _idthread = IDTH;
            _idexecucao = IDEX;

            // define os serviçcos que serão executados na OS
            var serv = (from tb in db.tb_osXservs where tb.FK_IDos == _idos select new { tb.FK_IDserv });
            servicosOS = new List<int>();
            foreach (var sk in serv)
            {
                servicosOS.Add((int)sk.FK_IDserv);
            }
        }

        public class binomino
        {
            public string EMPRESA { get; set; }
            public string FILIAL { get; set; }
            public string CNPJ { get; set; }
        }

        public void arrumaArquivo()
        { 

           

            string arqcons = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
            string arqtemp = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt";
            linhaComplex lc = new linhaComplex();

            Encoding enc = Encoding.GetEncoding("ISO-8859-1");
            StreamReader sr = new StreamReader(arqcons, enc);

            int count = 0;
            string linha = "";

            // carrega cnpjs consulta.txt na lista
            List<binomino> liBi = new List<binomino>();
            //List<string> cnpjs = new List<string>();
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    liBi.Add(new binomino() { EMPRESA = lc.OREMPRESA, FILIAL = lc.ORFILIAL, CNPJ = lc.ORCNPJ });
                   // cnpjs.Add(lc.ORCNPJ);
                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;

            sr = new StreamReader(arqtemp, enc);

            count = 0;
            linha = "";
            // compara com tempConsulta .txt e add em consulta apenas quem não esta em consulta
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    if ( ! liBi.Any(t=>t.EMPRESA == lc.OREMPRESA && t.FILIAL == lc.ORFILIAL && t.CNPJ == lc.ORCNPJ))
                    //if (  !cnpjs.Contains(lc.ORCNPJ))
                    {
                        FW.cIO.appendLine(arqcons, lc.LINHA);
                    }
                    
                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;
            // adiciona cnpj de consulta.txt na lista
            string arqorig = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";

            sr = new StreamReader(arqcons, enc);

            count = 0;
            linha = "";
            liBi = new List<binomino>();
            //cnpjs = new List<string>();
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    liBi.Add(new binomino() { EMPRESA = lc.OREMPRESA, FILIAL = lc.ORFILIAL, CNPJ = lc.ORCNPJ });
                    //cnpjs.Add(lc.ORCNPJ);
                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;
            // compara com baseoriginal .txt e add em consulta apenas quem não esta em consulta
            sr = new StreamReader(arqorig, enc);

            count = 0;
            linha = "";

            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    if (!liBi.Any(t => t.EMPRESA == lc.OREMPRESA && t.FILIAL == lc.ORFILIAL && t.CNPJ == lc.ORCNPJ))
                    //if (!cnpjs.Contains(lc.ORCNPJ))
                    {
                        FW.cIO.appendLine(arqcons, lc.LINHA);
                        log.info("foi encontrado CNPJ não processado na OS", _idos.ToString(), "Linha adicionada cnpj = " + lc.ORCNPJ.ToString());
                    }
                    else
                    { 
                    }

                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;


            // conta registros unicos
            sr = new StreamReader(arqorig, enc);

            count = 0;
            linha = "";

            liBi = new List<binomino>();
            //cnpjs = new List<string>();
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    
                    lc.autoCarga(linha);
                    liBi.Add(new binomino() { EMPRESA = lc.OREMPRESA, FILIAL = lc.ORFILIAL, CNPJ = lc.ORCNPJ });
                    //cnpjs.Add(lc.ORCNPJ);
                }

                count++;
            }

            int contagemUnicos = liBi.Distinct().Count();// cnpjs.Distinct().Count();
            log.info(string.Format("foram encontrados {0} CNPJs distintos na OS", contagemUnicos.ToString()), _idos.ToString(), "");
            sr.Close();
            sr.Dispose();
            sr = null;
            liBi.Clear();
            liBi = null;
            //cnpjs.Clear();
            //cnpjs = null;
            Thread.Sleep(2000);
        }

        public void arrumaArquivoPF()
        {

            string arqcons = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
            string arqtemp = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt";
            linhaComplexPFV1 lc = new linhaComplexPFV1();

            Encoding enc = Encoding.GetEncoding("ISO-8859-1");
            StreamReader sr = new StreamReader(arqcons, enc);

            int count = 0;
            string linha = "";

            // carrega cnpjs consulta.txt na lista
            List<binomino> liBi = new List<binomino>();
            //List<string> cnpjs = new List<string>();
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    liBi.Add(new binomino() { EMPRESA = lc.OREMPRESA, FILIAL = lc.ORFILIAL, CNPJ = lc.ORCPF });
                    // cnpjs.Add(lc.ORCNPJ);
                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;

            sr = new StreamReader(arqtemp, enc);

            count = 0;
            linha = "";
            // compara com tempConsulta .txt e add em consulta apenas quem não esta em consulta
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    if (!liBi.Any(t => t.EMPRESA == lc.OREMPRESA && t.FILIAL == lc.ORFILIAL && t.CNPJ == lc.ORCPF))
                    //if (  !cnpjs.Contains(lc.ORCNPJ))
                    {
                        FW.cIO.appendLine(arqcons, lc.LINHA);
                    }

                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;
            // adiciona cnpj de consulta.txt na lista
            string arqorig = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";

            sr = new StreamReader(arqcons, enc);

            count = 0;
            linha = "";
            liBi = new List<binomino>();
            //cnpjs = new List<string>();
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    liBi.Add(new binomino() { EMPRESA = lc.OREMPRESA, FILIAL = lc.ORFILIAL, CNPJ = lc.ORCPF });
                    //cnpjs.Add(lc.ORCNPJ);
                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;
            // compara com baseoriginal .txt e add em consulta apenas quem não esta em consulta
            sr = new StreamReader(arqorig, enc);

            count = 0;
            linha = "";

            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc.autoCarga(linha);
                    if (!liBi.Any(t => t.EMPRESA == lc.OREMPRESA && t.FILIAL == lc.ORFILIAL && t.CNPJ == lc.ORCPF))
                    //if (!cnpjs.Contains(lc.ORCNPJ))
                    {
                        FW.cIO.appendLine(arqcons, lc.LINHA);
                        log.info("foi encontrado CPF não processado na OS", _idos.ToString(), "Linha adicionada cpf = " + lc.ORCPF.ToString());
                    }
                    else
                    {
                    }

                }

                count++;
            }
            sr.Close();
            sr.Dispose();
            sr = null;


            // conta registros unicos
            sr = new StreamReader(arqorig, enc);

            count = 0;
            linha = "";

            liBi = new List<binomino>();
            //cnpjs = new List<string>();
            while (sr.Peek() >= 0)
            {

                linha = sr.ReadLine();
                if (count != 0)
                {

                    lc.autoCarga(linha);
                    liBi.Add(new binomino() { EMPRESA = lc.OREMPRESA, FILIAL = lc.ORFILIAL, CNPJ = lc.ORCPF });
                    //cnpjs.Add(lc.ORCNPJ);
                }

                count++;
            }

            int contagemUnicos = liBi.Distinct().Count();// cnpjs.Distinct().Count();
            log.info(string.Format("foram encontrados {0} CPFs distintos na OS", contagemUnicos.ToString()), _idos.ToString(), "");
            sr.Close();
            sr.Dispose();
            sr = null;
            liBi.Clear();
            liBi = null;
            //cnpjs.Clear();
            //cnpjs = null;
            Thread.Sleep(2000);
        }


        private void pararThread(long _qtd)
        {
            tb_thread ttd = db.tb_threads.Single(t => t.PK_IDt == _idthread);
            db.Refresh(System.Data.Linq.RefreshMode.KeepChanges, ttd);
            acaoThread = ttd.stts;
            waitThread = (int) ttd.wait;
            ttd.qtd = _qtd;
            db.SubmitChanges();
            ttd = null;
        }

        private void prepararArquivoPJ()
        {
            string linha = "";
            string saida = "";
            linhaComplex lc = new linhaComplex();
            int count = 0;
            Encoding enc = Encoding.GetEncoding("ISO-8859-1");
            StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/original.txt", enc);
            while (sr.Peek() >= 0)
            {
                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc = new linhaComplex();
                    lc.iniciar();
                    List<string> camps = FW.utils.linha2List(csv2quotecsv(linha));
                    lc.VR = "1";
                    lc.RF = "0";
                    lc.SI = "0";
                    lc.IB = "0";
                    lc.SN = servicosOS.Any(t => t == 6) ? "0" : "NOT";
                    lc.SU = servicosOS.Any(t => t == 3) ? "0" : "NOT";
                    lc.R0 = "NOT";
                    lc.R1 = "NOT";
                    lc.R2 = "NOT";
                    lc.R3 = "NOT";
                    lc.R4 = "NOT";
                    lc.ORCODIGO = camps[0];
                    lc.OREMPRESA = camps[1];
                    lc.ORFILIAL = camps[2];
                    lc.ORCNPJ = camps[3].Replace(".", "").Replace("/", "").Replace("-", "").Replace(" ", "").Replace("\"", "");

                    long pj = 0;
                    if (long.TryParse(lc.ORCNPJ, out pj))
                    {
                        lc.ORCNPJ = string.Format("{0:00000000000000}", Convert.ToInt64(lc.ORCNPJ));
                        if (!FW.validador.ValidaCnpj(lc.ORCNPJ))
                        {
                            lc.ORCNPJ = lc.ORCNPJ;
                            lc.RFERRCOD = "CNPJ INVALIDO";
                            lc.RFERRDESCRI = "CNPJ INVALIDO";
                            lc.RF = "ERR";
                            lc.SI = "ERR";
                            lc.IB = "ERR";
                            lc.SN = "ERR";
                            lc.SU = "ERR";
                        }
                    }
                    else
                    {
                        lc.ORCNPJ = lc.ORCNPJ;
                        lc.RFERRCOD = "CNPJ INVALIDO";
                        lc.RFERRDESCRI = "CNPJ INVALIDO";
                        lc.RF = "ERR";
                        lc.SI = "ERR";
                        lc.IB = "ERR";
                        lc.SN = "ERR";
                        lc.SU = "ERR";
                    }
                    
                    //lc.ORCNPJ = string.Format("{0:00000000000000}", Convert.ToInt64(lc.ORCNPJ));
                    
                    
                    lc.ORRAZAO = camps[4];
                    lc.ORFANTASIA = camps[5];
                    lc.ORLOGRADOURO = camps[6];
                    lc.ORNUM = camps[7];
                    lc.ORCOMPLEMENTO = camps[8];
                    lc.ORBAIRRO = camps[9];
                    lc.ORMUNICIPIO = camps[10];
                    lc.ORUF = camps[11];
                    lc.ORCEP = camps[12];
                    lc.ORIE = camps[13];
                    lc.ORIBGE = camps[14];
                    lc.ORCCM = camps[15];
                    lc.ORCNAE = camps[16];
                    lc.ORSUFRAMA = camps[17];

                    saida = saida + lc.LINHA + "\r\n";
                }
                else
                {
                    saida = saida + lc.CABECALHO + "\r\n";
                }
                count++;
            }
            sr.Dispose();
            sr = null;
            lc = null;

            cIO cio = new cIO();
            cio.CriaDir(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/comprovantes");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls");
            cio.NomeArquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";
            cio.Conteudo = saida;
            cio.GravarArquivo();

            cio.NomeArquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt";
            cio.GravarArquivo();
            cio = null;
        }

        private void prepararArquivoPF()
        {
            string linha = "";
            string saida = "";
            linhaComplexPFV1 lc = new linhaComplexPFV1();
            int count = 0;
            Encoding enc = Encoding.GetEncoding("ISO-8859-1");
            StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/original.txt", enc);
            while (sr.Peek() >= 0)
            {
                linha = sr.ReadLine();
                if (count != 0)
                {
                    lc = new linhaComplexPFV1();
                    lc.iniciar();
                    List<string> camps = FW.utils.linha2List(csv2quotecsv(linha));
                    lc.VR = "1";
                    lc.RF = "0";
                    lc.CO = "0";
                    lc.IB = "0";
                    lc.SI = "NOT"; // servicosOS.Any(t => t == 6) ? "0" : "NOT";
                    lc.R5 = "NOT";
                    lc.R0 = "NOT";
                    lc.R1 = "NOT";
                    lc.R2 = "NOT";
                    lc.R3 = "NOT";
                    lc.R4 = "NOT";
                    lc.ORCODIGO = camps[0];
                    lc.OREMPRESA = camps[1];
                    lc.ORFILIAL = camps[2];

                    lc.ORCPF = camps[3].Replace(".", "").Replace("/", "").Replace("-", "").Replace(" ", "").Replace("\"", "");
                    
                    lc.ORCPF = string.Format("{0:00000000000}", Convert.ToInt64(lc.ORCPF));

                    long pf = 0;
                    if (long.TryParse(lc.ORCPF, out pf))
                    {
                        lc.ORCPF = string.Format("{0:00000000000}", Convert.ToInt64(lc.ORCPF));
                        if (!FW.validador.ValidaCPF(lc.ORCPF))
                        {
                            lc.ORCPF = lc.ORCPF;
                            lc.RFERRCOD = "CPF INVALIDO";
                            lc.RFERRDESCRI = "CPF INVALIDO";
                            lc.RF = "ERR";
                            lc.SI = "ERR";
                            lc.IB = "ERR";
                            lc.CO = "ERR";
                           
                        }
                    }
                    else
                    {
                        lc.ORCPF = lc.ORCPF;
                        lc.RFERRCOD = "CPF INVALIDO";
                        lc.RFERRDESCRI = "CPF INVALIDO";
                        lc.RF = "ERR";
                        lc.SI = "ERR";
                        lc.IB = "ERR";
                        lc.CO = "ERR";
                       
                    }


                    lc.ORNOME = camps[4];
                    lc.ORLOGRADOURO = camps[5];
                    lc.ORNUM = camps[6];
                    lc.ORCOMPLEMENTO = camps[7];
                    lc.ORBAIRRO = camps[8];
                    lc.ORMUNICIPIO = camps[9];
                    lc.ORUF = camps[10];
                    lc.ORCEP = camps[11];
                    lc.ORIBGE = camps[12];

                    saida = saida + lc.LINHA + "\r\n";
                }
                else
                {
                    saida = saida + lc.CABECALHO + "\r\n";
                }
                count++;
            }
            sr.Dispose();
            sr = null;
            lc = null;
            cIO cio = new cIO();
            cio.CriaDir(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/comprovantes");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt");
            cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls");
            cio.NomeArquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";
            cio.Conteudo = saida;
            cio.GravarArquivo();

            cio.NomeArquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt";
            cio.GravarArquivo();

            cio = null;
        }                                                                                                                                      



        public void consultaPJ()
        {
            prepararArquivoPJ();
           
            // stts = 4 em processamento
            tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
            o.status = "Executando Consulta";
            o.stts = 4;
            db.SubmitChanges();
            o = null;
           

            disparaConsultasPJ();


            
        }

        public void reConsultaPJ()
        {
            // stts = 4 em processamento
            tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
            o.status = "Executando Reconsulta";
            o.stts = 4;
            db.SubmitChanges();
            o = null;
            // fim stts
            //int tentativas = 0;
            //bool continuar = false;
            ////string tipoLinha = "";
            ////string linha = "";
            ////string cnpj = "";
            ////int count = 0;
            //try
            //{
            //    cIO cio = new cIO();
            //    cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
            //    cio.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
            //    cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
            //    cio = null;
            //    continuar = true;
            //}
            //catch (Exception ex)
            //{
            //    log.erro("reConsultaPJ acesso aos arquivos", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            //}

            //if (continuar)
            if (reorganizaArquivoReconsulta())
            {
                disparaConsultasPJ();
            }
            else
            {
                o = db.tb_os.Single(t => t.PK_IDos == _idos);
                o.status = "Aguardando para executar reconsulta";
                o.dt_proxExec = DateTime.Now.AddMinutes(5d);
                o.stts = 5;
                db.SubmitChanges();
                o = null;
            }

         
        }

        public void consultaPF()
        {
            prepararArquivoPF();

            // stts = 4 em processamento
            tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
            o.status = "Executando Consulta";
            o.stts = 4;
            db.SubmitChanges();
            o = null;


            disparaConsultasPF();

        }

        public void reConsultaPF()
        {
            tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
            o.status = "Executando Reconsulta";
            o.stts = 4;
            db.SubmitChanges();
            o = null;

            if (reorganizaArquivoReconsultaPF())
            {
                disparaConsultasPF();
            }
            else
            {
                o = db.tb_os.Single(t => t.PK_IDos == _idos);
                o.status = "Aguardando para executar reconsulta";
                o.dt_proxExec = DateTime.Now.AddMinutes(5d);
                o.stts = 5;
                db.SubmitChanges();
                o = null;
            }

        }


        private bool reorganizaArquivoReconsulta()
        {
            bool continuar = false;
            string linha = "";
            int count = 0;
            try
            {
                cIO cio = new cIO();
                string temparquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt";
                cio.apagaArq(temparquivo);
                cio = null;
                linhaComplex lc = new linhaComplex();
                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", enc);

                log.debug(string.Format("reorganiza Arquivo Consulta IDOS {0} ", _idos.ToString()));

                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    if (count != 0)
                    {
                        lc = new linhaComplex();
                        lc.autoCarga(linha);
                        //lc.VR = "1";
                        //lc.RF = "0";
                        //lc.SI = "0";
                        //lc.IB = "0";
                        if (lc.SN == "NOT")
                        {
                            lc.SN = servicosOS.Any(t => t == 6) ? "0" : "NOT";
                        }
                        if (lc.SU == "NOT")
                        {
                            lc.SU = servicosOS.Any(t => t == 3) ? "0" : "NOT";
                        }
                        //lc.R0 = "NOT";
                        //lc.R1 = "NOT";
                        //lc.R2 = "NOT";
                        //lc.R3 = "NOT";
                        //lc.R4 = "NOT";
                        FW.cIO.appendLine(temparquivo, lc.LINHA);
                    }
                    else
                    {
                        FW.cIO.appendLine(temparquivo, lc.CABECALHO);
                    }
                    count++;
                }

                //cio.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
                sr.Close();
                sr.Dispose();
                sr = null;
                cio = new cIO();
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
                cio = null;
                continuar = true;
            }
            catch (Exception ex)
            {
                log.erro("reorganizaArquivoReconsulta acesso aos arquivos", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }

            return continuar;
        }

        private bool reorganizaArquivoReconsultaPF()
        {
            bool continuar = false;
            string linha = "";
            int count = 0;
            try
            {
                cIO cio = new cIO();
                string temparquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt";
                cio.apagaArq(temparquivo);
                cio = null;
                linhaComplexPFV1 lc = new linhaComplexPFV1();
                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", enc);

                log.debug(string.Format("reorganiza Arquivo Consulta IDOS {0} ", _idos.ToString()));

                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    if (count != 0)
                    {
                        lc = new linhaComplexPFV1();
                        lc.autoCarga(linha);
                        //lc.VR = "1";
                        //lc.RF = "0";
                        //lc.CO = "0";
                        //lc.IB = "0";
                        if (lc.SI == "NOT")
                        {
                            lc.SI = servicosOS.Any(t => t == 6) ? "0" : "NOT";
                        }
                        //if (lc.SU == "NOT")
                        //{
                        //    lc.SU = servicosOS.Any(t => t == 3) ? "0" : "NOT";
                        //}
                        //lc.R0 = "NOT";
                        //lc.R1 = "NOT";
                        //lc.R2 = "NOT";
                        //lc.R3 = "NOT";
                        //lc.R4 = "NOT";
                        FW.cIO.appendLine(temparquivo, lc.LINHA);
                    }
                    else
                    {
                        FW.cIO.appendLine(temparquivo, lc.CABECALHO);
                    }
                    count++;
                }

                //cio.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
                sr.Close();
                sr.Dispose();
                sr = null;
                cio = new cIO();
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
                cio = null;
                continuar = true;
            }
            catch (Exception ex)
            {
                log.erro("reorganizaArquivoReconsultaPF acesso aos arquivos", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }

            return continuar;
        }

        private void disparaConsultasPJ()
        {

            try
            {
                slots = new Thread[csrv.QTDTHREADSPOROS];
                for (int i = 0; i < slots.Count(); i++)
                {
                    slots[i] = new Thread(delegate() { Thread.Sleep(1); });
                }
                string rodar = "";
                int qtd = 0;
                string linha = "";
                int count = 0;
                string arquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
                linhaComplex lc = new linhaComplex();

                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt", enc);

                log.debug(string.Format("abrindo consultas IDOS {0} com {1} threads", _idos.ToString(), csrv.QTDTHREADSPOROS.ToString()));

                while (sr.Peek() >= 0)
                {

                    linha = sr.ReadLine();
                    if (count != 0)
                    {
                        lc = new linhaComplex();
                        lc.autoCarga(linha);
                        if (acaoThread == "RUN")
                        {
                            try
                            {
                                if (lc.RF != "OK" && lc.RF != "ERR" && lc.RF != "CNL" && lc.RF != "NOT")
                                {
                                    // receita federal
                                    if (int.TryParse(lc.RF, out qtd))
                                    {
                                        rodar = "RF";
                                    }
                                    else
                                    {
                                        rodar = "RFERRQTD";
                                    }
                                }
                                else
                                {
                                    if (lc.RF == "OK")
                                    {
                                        if (lc.SI != "OK" && lc.SI != "ERR" && lc.SI != "CNL" && lc.SI != "NOT")
                                        {
                                            //sintegra
                                            if (int.TryParse(lc.SI, out qtd))
                                            {
                                                rodar = "SI";
                                            }
                                            else
                                            {
                                                rodar = "SIERRQTD";
                                            }
                                        }
                                        else
                                        {
                                            if (lc.IB != "OK" && lc.IB != "ERR" && lc.IB != "CNL" && lc.IB != "NOT")
                                            {
                                                //ibge
                                                if (int.TryParse(lc.IB, out qtd))
                                                {
                                                    rodar = "IB";
                                                }
                                                else
                                                {
                                                    rodar = "IBERRQTD";
                                                }
                                            }
                                            else
                                            {
                                                if (lc.SN != "OK" && lc.SN != "ERR" && lc.SN != "CNL" && lc.SN != "NOT")
                                                {
                                                    //Simples Nacional
                                                    if (int.TryParse(lc.SN, out qtd))
                                                    {
                                                        rodar = "SN";
                                                    }
                                                    else
                                                    {
                                                        rodar = "SNERRQTD";
                                                    }
                                                }
                                                else
                                                {
                                                    if (lc.SU != "OK" && lc.SU != "ERR" && lc.SU != "CNL" && lc.SU != "NOT")
                                                    {
                                                        //SUFRAMA
                                                        // somente CNPJ do AM
                                                        if (lc.RFESTADO == "AM")
                                                        {
                                                            if (int.TryParse(lc.SU, out qtd))
                                                            {
                                                                rodar = "SU";
                                                            }
                                                            else
                                                            {
                                                                rodar = "SUERRQTD";
                                                            }
                                                        }
                                                        else
                                                        {
                                                            rodar = "SUNAOAM";
                                                        }
                                                    }
                                                    else
                                                    {
                                                        rodar = "REPETE";
                                                    }
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        rodar = "RFERR";
                                    }
                                }

                                // executa consultas


                                switch (rodar)
                                {
                                    case "REPETE":
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "RFERR":
                                        lc.SI = "CNL";
                                        lc.IB = "CNL";
                                        lc.SN = "CNL";
                                        lc.SU = "CNL";
                                        lc.R0 = "CNL";
                                        lc.R1 = "CNL";
                                        lc.R2 = "CNL";
                                        lc.R3 = "CNL";
                                        lc.R4 = "CNL";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "RF":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {

                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCNPJRFV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "RFERRQTD":
                                        lc.RF = "ERR";
                                        lc.RFERRCOD = "QTD not int";
                                        lc.RFERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "SI":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        //sintegra
                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCNPJSIV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "SIERRQTD":
                                        lc.SI = "ERR";
                                        lc.SIERRCOD = "QTD not int";
                                        lc.SIERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);

                                        break;
                                    case "IB":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        // ibge
                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCNPJIBGEV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "IBERRQTD":
                                        lc.SI = "ERR";
                                        //lc.RFERRCOD = "QTD not int";
                                        //lc.RFERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);

                                        break;
                                    case "SN":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        // simples
                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCNPJSNV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "SNERRQTD":
                                        lc.SN = "ERR";
                                        lc.SNERRCOD = "QTD not int";
                                        lc.SNERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);

                                        break;
                                    case "SU":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        // suframa
                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCNPJSUV1(lc, _idos, _idemp); });
                                                        slots[it].Start();


                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "SUERRQTD":
                                        lc.SU = "ERR";
                                        //lc.SNERRCOD = "QTD not int";
                                        //lc.SNERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "SUNAOAM":
                                        lc.SU = "OK";
                                        lc.SUERRCOD = "30-1";
                                        lc.SUERRDESCRI = "CNPJ não pertence ao AM";
                                        //lc.SNERRCOD = "QTD not int";
                                        //lc.SNERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                }

                            }
                            catch (Exception ex)
                            {
                                lc.RF = "ERR";
                                lc.RFERRCOD = "CONSULTAPJ-RF";
                                lc.RFERRDESCRI = ex.Message.Replace("\"", "");
                                FW.cIO.appendLine(arquivo, lc.LINHA);
                                log.erro("disparaConsultasPJ", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
                            }
                        }
                        else if (acaoThread == "CNL")
                        {
                            // Se thread OS cancelada

                            lc.RF = lc.RF != "OK" ? "CNL" : lc.RF;
                            lc.SI = lc.SI != "OK" ? "CNL" : lc.SI;
                            lc.IB = lc.IB != "OK" ? "CNL" : lc.IB;
                            lc.SN = lc.SN != "OK" ? "CNL" : lc.SN;
                            lc.SU = lc.SU != "OK" ? "CNL" : lc.SU;
                            lc.R0 = lc.R0 != "OK" ? "CNL" : lc.R0;
                            lc.R1 = lc.R1 != "OK" ? "CNL" : lc.R1;
                            lc.R2 = lc.R2 != "OK" ? "CNL" : lc.R2;
                            lc.R3 = lc.R3 != "OK" ? "CNL" : lc.R3;
                            lc.R4 = lc.R4 != "OK" ? "CNL" : lc.R4;

                            FW.cIO.appendLine(arquivo, lc.LINHA);
                        }
                        else
                        {
                            FW.cIO.appendLine(arquivo, lc.LINHA);
                        }
                    }
                    // count == 0
                    else
                    {
                        FW.cIO.appendLine(arquivo, lc.CABECALHO);
                    }
                    count++;

                    pararThread(count);
                    Thread.Sleep(waitThread);

                }
                sr.DiscardBufferedData();
                sr.Dispose();
                sr = null;

                // cio = null;

                // espera para todas as threads terminarem.
                Thread.Sleep(180000);

                procurarErrosConsulta();
            }
            catch (Exception ex)
            {
                log.erro("disparaConsultasPJ geral", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }


        private void disparaConsultasPF()
        {

            try
            {
                slots = new Thread[csrv.QTDTHREADSPOROS];
                for (int i = 0; i < slots.Count(); i++)
                {
                    slots[i] = new Thread(delegate() { Thread.Sleep(1); });
                }
                string rodar = "";
                int qtd = 0;
                string linha = "";
                int count = 0;
                string arquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
                linhaComplexPFV1 lc = new linhaComplexPFV1();

                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt", enc);

                log.debug(string.Format("abrindo consultas IDOS {0} com {1} threads", _idos.ToString(), csrv.QTDTHREADSPOROS.ToString()));

                while (sr.Peek() >= 0)
                {

                    linha = sr.ReadLine();
                    if (count != 0)
                    {
                        lc = new linhaComplexPFV1();
                        lc.autoCarga(linha);
                        if (acaoThread == "RUN")
                        {
                            try
                            {
                                if (lc.RF != "OK" && lc.RF != "ERR" && lc.RF != "CNL" && lc.RF != "NOT")
                                {
                                    // receita federal
                                    if (int.TryParse(lc.RF, out qtd))
                                    {
                                        rodar = "RF";
                                    }
                                    else
                                    {
                                        rodar = "RFERRQTD";
                                    }
                                }
                                else
                                {
                                    if (lc.RF == "OK")
                                    {
                                        if (lc.CO != "OK" && lc.CO != "ERR" && lc.CO != "CNL" && lc.CO != "NOT")
                                        {
                                            //CORREIO
                                            if (int.TryParse(lc.CO, out qtd))
                                            {
                                                rodar = "CO";
                                            }
                                            else
                                            {
                                                rodar = "COERRQTD";
                                            }
                                        }
                                        else
                                        {
                                            if (lc.IB != "OK" && lc.IB != "ERR" && lc.IB != "CNL" && lc.IB != "NOT")
                                            {
                                                //ibge
                                                if (int.TryParse(lc.IB, out qtd))
                                                {
                                                    rodar = "IB";
                                                }
                                                else
                                                {
                                                    rodar = "IBERRQTD";
                                                }
                                            }
                                            else
                                            {
                                                if (lc.SI != "OK" && lc.SI != "ERR" && lc.SI != "CNL" && lc.SI != "NOT")
                                                {
                                                    //Sintegra
                                                    if (int.TryParse(lc.SI, out qtd))
                                                    {
                                                        rodar = "SI";
                                                    }
                                                    else
                                                    {
                                                        rodar = "SIERRQTD";
                                                    }
                                                }
                                                else
                                                {
                                           
                                                        rodar = "REPETE";
                                 
                                                }
                                            }
                                        }
                                    }
                                    else
                                    {
                                        rodar = "RFERR";
                                    }
                                }

                                // executa consultas


                                switch (rodar)
                                {
                                    case "REPETE":
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "RFERR":
                                        lc.SI = "CNL";
                                        lc.IB = "CNL";
                                        lc.CO = "CNL";
                                        lc.R5 = "CNL";
                                        lc.R0 = "CNL";
                                        lc.R1 = "CNL";
                                        lc.R2 = "CNL";
                                        lc.R3 = "CNL";
                                        lc.R4 = "CNL";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "RF":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {

                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCPFRFV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "RFERRQTD":
                                        lc.RF = "ERR";
                                        lc.RFERRCOD = "QTD not int";
                                        lc.RFERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);
                                        break;
                                    case "SI":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        //sintegra
                                                        //slots[it] = new Thread(delegate() { consultasThread.consultaCPF(lc, _idos, _idemp); });
                                                        //slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "SIERRQTD":
                                        lc.SI = "ERR";
                                        lc.SIERRCOD = "QTD not int";
                                        lc.SIERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);

                                        break;
                                    case "IB":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        // ibge
                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCPFIBGEV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "IBERRQTD":
                                        lc.SI = "ERR";
                                        //lc.RFERRCOD = "QTD not int";
                                        //lc.RFERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);

                                        break;
                                    case "CO":
                                        while (true)
                                        {
                                            if (slots.Any(t => t.IsAlive == false))
                                            {
                                                for (int it = 0; it < slots.Count(); it++)
                                                {
                                                    if (slots[it].IsAlive == false)
                                                    {
                                                        // simples
                                                        slots[it] = new Thread(delegate() { consultasThread.consultaCPFCORREIOV1(lc, _idos, _idemp); });
                                                        slots[it].Start();

                                                        break;
                                                    }
                                                }
                                                break;
                                            }
                                            else
                                            {
                                                Thread.Sleep(1000);
                                            }
                                        }
                                        break;
                                    case "COERRQTD":
                                        lc.CO = "ERR";
                                        lc.COERRCOD = "QTD not int";
                                        lc.COERRDESCRI = "QTD definida no campo RF não é inteiro";
                                        FW.cIO.appendLine(arquivo, lc.LINHA);

                                        break;
                                }

                            }
                            catch (Exception ex)
                            {
                                lc.RF = "ERR";
                                lc.RFERRCOD = "CONSULTAPJ-RF";
                                lc.RFERRDESCRI = ex.Message.Replace("\"", "");
                                FW.cIO.appendLine(arquivo, lc.LINHA);
                                log.erro("disparaConsultasPJ", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
                            }
                        }
                        else if (acaoThread == "CNL")
                        {
                            // Se thread OS cancelada

                            lc.RF = lc.RF != "OK" ? "CNL" : lc.RF;
                            lc.SI = lc.SI != "OK" ? "CNL" : lc.SI;
                            lc.IB = lc.IB != "OK" ? "CNL" : lc.IB;
                            lc.CO = lc.CO != "OK" ? "CNL" : lc.CO;
                            lc.R5 = lc.R5 != "OK" ? "CNL" : lc.R5;
                            lc.R0 = lc.R0 != "OK" ? "CNL" : lc.R0;
                            lc.R1 = lc.R1 != "OK" ? "CNL" : lc.R1;
                            lc.R2 = lc.R2 != "OK" ? "CNL" : lc.R2;
                            lc.R3 = lc.R3 != "OK" ? "CNL" : lc.R3;
                            lc.R4 = lc.R4 != "OK" ? "CNL" : lc.R4;

                            FW.cIO.appendLine(arquivo, lc.LINHA);
                        }
                        else
                        {
                            FW.cIO.appendLine(arquivo, lc.LINHA);
                        }
                    }
                    // count == 0
                    else
                    {
                        FW.cIO.appendLine(arquivo, lc.CABECALHO);
                    }
                    count++;

                    pararThread(count);
                    Thread.Sleep(waitThread);

                }
                sr.DiscardBufferedData();
                sr.Dispose();
                sr = null;

                // cio = null;

                // espera para todas as threads terminarem.
                Thread.Sleep(180000);

                procurarErrosConsultaPF();
            }
            catch (Exception ex)
            {
                log.erro("disparaConsultasPF geral", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }


        private void procurarErrosConsultaPF()
        {
            try
            {
                int qtd = 0;
                string linha = "";
                int count = 0;
                linhaComplexPFV1 lc = new linhaComplexPFV1();
                bool erros = false;
                try
                {
                    Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                    StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", enc);
                    while (sr.Peek() >= 0)
                    {
                        linha = sr.ReadLine();


                        if (count != 0)
                        {
                            lc = new linhaComplexPFV1();
                            lc.autoCarga(linha);
                            if (lc.RF != "OK" && lc.RF != "ERR" && lc.RF != "CNL" && lc.RF != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.SI != "OK" && lc.SI != "ERR" && lc.SI != "CNL" && lc.SI != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.IB != "OK" && lc.IB != "ERR" && lc.IB != "CNL" && lc.IB != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.CO != "OK" && lc.CO != "ERR" && lc.CO != "CNL" && lc.CO != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R5 != "OK" && lc.R5 != "ERR" && lc.R5 != "CNL" && lc.R5 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R0 != "OK" && lc.R0 != "ERR" && lc.R0 != "CNL" && lc.R0 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R1 != "OK" && lc.R1 != "ERR" && lc.R1 != "CNL" && lc.R1 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R2 != "OK" && lc.R2 != "ERR" && lc.R2 != "CNL" && lc.R2 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R3 != "OK" && lc.R3 != "ERR" && lc.R3 != "CNL" && lc.R3 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R4 != "OK" && lc.R4 != "ERR" && lc.R4 != "CNL" && lc.R4 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                        }
                        count++;
                    }
                    sr.DiscardBufferedData();
                    sr.Dispose();
                    sr = null;
                }
                catch (Exception ex)
                {
                    log.erro("procurarErrosConsulta PF acesso ao arquivo", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
                }
                string _t_status = "";
                int _t_stts = 0;
                DateTime _t_dt_proxExec = DateTime.Now;

                //o.stts = (erros) ? 5 : 9;
                if (acaoThread == "RUN")
                {
                    if (qtd > 0)
                    {
                        _t_status = "Aguardando para executar reconsulta";
                        _t_stts = 5;
                        _t_dt_proxExec = DateTime.Now.AddMinutes(5d);
                        acaoThread = "FIM";
                    }
                    else
                    {
                        _t_status = "Aguardando fechamento de arquivos";
                        _t_stts = 9;
                        _t_dt_proxExec = DateTime.Now.AddMinutes(5d);
                        acaoThread = "FIM";
                    }
                }
                else
                {
                    _t_status = "Thread Cancelada";
                    _t_stts = 6;
                    _t_dt_proxExec = DateTime.Now;
                    acaoThread = "CNL";
                }
                tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
                db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, o);
                o.status = _t_status;
                o.stts = _t_stts;
                o.dt_proxExec = _t_dt_proxExec;
                db.SubmitChanges();
                o = null;
            }
            catch (Exception ex)
            {
                log.erro("procurarErrosConsultaPF", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }

        }

        private void procurarErrosConsulta()
        {
            try
            {
                int qtd = 0;
                string linha = "";
                int count = 0;
                linhaComplex lc = new linhaComplex();
                bool erros = false;
                try
                {
                    Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                    StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", enc);
                    while (sr.Peek() >= 0)
                    {
                        linha = sr.ReadLine();


                        if (count != 0)
                        {
                            lc = new linhaComplex();
                            lc.autoCarga(linha);
                            if (lc.RF != "OK" && lc.RF != "ERR" && lc.RF != "CNL" && lc.RF != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.SI != "OK" && lc.SI != "ERR" && lc.SI != "CNL" && lc.SI != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.IB != "OK" && lc.IB != "ERR" && lc.IB != "CNL" && lc.IB != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.SN != "OK" && lc.SN != "ERR" && lc.SN != "CNL" && lc.SN != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.SU != "OK" && lc.SU != "ERR" && lc.SU != "CNL" && lc.SU != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R0 != "OK" && lc.R0 != "ERR" && lc.R0 != "CNL" && lc.R0 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R1 != "OK" && lc.R1 != "ERR" && lc.R1 != "CNL" && lc.R1 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R2 != "OK" && lc.R2 != "ERR" && lc.R2 != "CNL" && lc.R2 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R3 != "OK" && lc.R3 != "ERR" && lc.R3 != "CNL" && lc.R3 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }
                            if (lc.R4 != "OK" && lc.R4 != "ERR" && lc.R4 != "CNL" && lc.R4 != "NOT")
                            {
                                erros = true;
                                qtd++;
                            }


                            //if (lc.RF != "OK" && lc.RF != "ERR" && lc.RF != "CNL" && lc.RF != "NOT" &&
                            //    lc.SI != "OK" && lc.SI != "ERR" && lc.SI != "CNL" && lc.SI != "NOT" &&
                            //    lc.IB != "OK" && lc.IB != "ERR" && lc.IB != "CNL" && lc.IB != "NOT" &&
                            //    lc.SN != "OK" && lc.SN != "ERR" && lc.SN != "CNL" && lc.SN != "NOT" &&
                            //    lc.SU != "OK" && lc.SU != "ERR" && lc.SU != "CNL" && lc.SU != "NOT" &&
                            //    lc.R0 != "OK" && lc.R0 != "ERR" && lc.R0 != "CNL" && lc.R0 != "NOT" &&
                            //    lc.R1 != "OK" && lc.R1 != "ERR" && lc.R1 != "CNL" && lc.R1 != "NOT" &&
                            //    lc.R2 != "OK" && lc.R2 != "ERR" && lc.R2 != "CNL" && lc.R2 != "NOT" &&
                            //    lc.R3 != "OK" && lc.R3 != "ERR" && lc.R3 != "CNL" && lc.R3 != "NOT" &&
                            //    lc.R4 != "OK" && lc.R4 != "ERR" && lc.R4 != "CNL" && lc.R4 != "NOT"
                            //    )
                           
                            ////if (int.TryParse(lc.RF, out qtd) ||
                            ////    int.TryParse(lc.SI, out qtd) ||
                            ////    int.TryParse(lc.IB, out qtd) ||
                            ////    int.TryParse(lc.SN, out qtd) ||
                            ////    int.TryParse(lc.SU, out qtd) ||
                            ////    int.TryParse(lc.R0, out qtd) ||
                            ////    int.TryParse(lc.R1, out qtd) ||
                            ////    int.TryParse(lc.R2, out qtd) ||
                            ////    int.TryParse(lc.R3, out qtd) ||
                            ////    int.TryParse(lc.R4, out qtd))
                            //{
                            //    erros = true;
                            //    qtd++;
                            //}
                            //else
                            //{
                            //    erros = false;
                            //}
                            ////cio.appendLine("\"OR\";" + linha);
                            //List<string> campos = linha2List(linha);
                            //tipoLinha = campos[0].ToString();
                            //if (tipoLinha.Contains("err"))
                            //{
                            //    erros = true;         
                            //}
                        }
                        count++;
                    }
                    sr.DiscardBufferedData();
                    sr.Dispose();
                    sr = null;
                }
                catch (Exception ex)
                {
                    log.erro("procurarErrosConsulta acesso ao arquivo", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
                }
                string _t_status = "";
                int _t_stts = 0;
                DateTime _t_dt_proxExec = DateTime.Now;

                //o.stts = (erros) ? 5 : 9;
                if (acaoThread == "RUN")
                {
                    if (qtd > 0)
                    {
                        _t_status = "Aguardando para executar reconsulta";
                        _t_stts = 5;
                        _t_dt_proxExec = DateTime.Now.AddMinutes(5d);
                        acaoThread = "FIM";
                    }
                    else
                    {
                        _t_status = "Aguardando fechamento de arquivos";
                        _t_stts = 9;
                        _t_dt_proxExec = DateTime.Now.AddMinutes(5d);
                        acaoThread = "FIM";
                    }
                }
                else
                {
                    _t_status = "Thread Cancelada";
                    _t_stts = 6;
                    _t_dt_proxExec = DateTime.Now;
                    acaoThread = "CNL";
                }
                tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
                db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, o);
                o.status = _t_status;
                o.stts = _t_stts;
                o.dt_proxExec = _t_dt_proxExec;
                db.SubmitChanges();
                o = null;
            }
            catch (Exception ex)
            {
                log.erro("procurarErrosConsulta", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }


       

        private string csv2quotecsv(string _linha)
        {
            string ret = "";
            bool achouAspas = false;
            _linha = _linha.Replace("\n", "").Replace("\r", "");
            string campo = "";
            List<string> campos = new List<string>();

            for (int i = 0; i < _linha.Length; i++)
            {
                if ((_linha.Substring(i, 1) == ";"  ) && !achouAspas)
                {
                    campos.Add(campo);
                    campo = "";
                }
                else
                {
                    if (_linha.Substring(i, 1) == "\"")
                    {
                        achouAspas = achouAspas ? false : true;
                    }
                    else
                    {
                        campo = campo + _linha.Substring(i, 1);
                    }
                }
                if ((i + 1) == _linha.Length)
                {
                    campos.Add(campo);
                }
            }

            foreach (string s in campos)
            {
                ret = ret + "\"" + s + "\";";
            }


            return ret;
        }

        private List<string> linha2List(string _linha)
        {
            List<string> ls = new List<string>();
            string campo = "";
            bool a1 = false; // primeira aspas
            bool a2 = false; // segunda aspas
            for (int x = 0; x < _linha.Length - 1; x++)
            {
                if (_linha.Substring(x, 1) == "\"")
                {
                    if (a1 && !a2)
                    {
                        a1 = false;
                        a2 = true;
                    }
                    else if (!a1 && a2)
                    {
                        a1 = true;
                        a2 = false;
                    }
                    else
                    {
                        a1 = true;
                        a2 = false;
                    }
                }

                if (a1 && !a2 && _linha.Substring(x, 1) != "\"")
                {
                    campo = campo + _linha.Substring(x, 1);
                }

                if (!a1 && a2 && _linha.Substring(x, 1) == "\"")
                {
                    ls.Add(campo);
                    campo = "";
                }

            }


            return ls;
        }

        private string cortaString(string _original, int _tamanho)
        {
            string ret = "";
            if (_original.Length > _tamanho)
            {
                ret = _original.Substring(0, _tamanho);
            }
            else
            {
                ret = _original;
            }
            return ret;

        }

        private string original2linhaSimple(string _orig)
        {
            linhaSimple ls = new linhaSimple();
            List<string> campos = linha2List(_orig);
            ls.TIPOVERIFICACAO = "OR";
            ls.CODIGO = campos[0].Replace("\"", "");
            ls.EMPRESA = campos[1].Replace("\"", "");
            ls.FILIAL = campos[2].Replace("\"", "");
            ls.CNPJ = campos[3].Replace("\"", "");
            ls.RAZAO = campos[4].Replace("\"", "");
            ls.FANTASIA = campos[5].Replace("\"", "");
            ls.LOGRADOURO = campos[6].Replace("\"", "");
            ls.NUMERO = campos[7].Replace("\"", "");
            ls.COMPLEMENTO = campos[8].Replace("\"", "");
            ls.BAIRRO = campos[9].Replace("\"", "");
            ls.MUNICIPIO = campos[10].Replace("\"", "");
            ls.UF = campos[11].Replace("\"", "");
            ls.CEP = campos[12].Replace("\"", "");
            ls.IE = campos[13].Replace("\"", "");
            ls.IBGE = campos[14].Replace("\"", "");
            ls.CCM = campos[15].Replace("\"", "");
            ls.CNAE = campos[16].Replace("\"", "");
            ls.SUFRAMA = campos[17].Replace("\"", "");
            ls.QTDTENTATIVAS = "1";
            ls.normatiza();
            return ls.LINHA;
        }

        private linhaSimplePF original2linhaSimplePF(string _orig)
        {
            linhaSimplePF ls = new linhaSimplePF();
            List<string> campos = linha2List(_orig);
            ls.TIPOVERIFICACAO = "OR";
            ls.CODIGO = campos[0].Replace("\"", "");
            ls.EMPRESA = campos[1].Replace("\"", "");
            ls.FILIAL = campos[2].Replace("\"", "");
            ls.CPF = campos[3].Replace("\"", "");
            ls.NOME = campos[4].Replace("\"", "");
            ls.LOGRADOURO = campos[5].Replace("\"", "");
            ls.NUMERO = campos[6].Replace("\"", "");
            ls.COMPLEMENTO = campos[7].Replace("\"", "");
            ls.BAIRRO = campos[8].Replace("\"", "");
            ls.MUNICIPIO = campos[9].Replace("\"", "");
            ls.UF = campos[10].Replace("\"", "");
            ls.CEP = campos[11].Replace("\"", "");
            ls.IBGE = campos[12].Replace("\"", "");
            ls.normatiza();
            return ls; //.LINHA;
        }






        private void Reg2Nreg()
        {
            try
            {
                int count = 0;
                string linha = "";

                cIO cio = new cIO();
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt");
                cio = null;

                linhaComplex lc = new linhaComplex();
                linhaSimple ls = new linhaSimple();
                string csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
                string csvsaida = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";

                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csvfile, enc);
                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    //cio.appendLine("\"OR\";" + linha);


                    if (count != 0)
                    {
                        // OR
                        ls = new linhaSimple();
                        lc.autoCarga(linha);

                        ls.BAIRRO = lc.ORBAIRRO;
                        ls.CCM = lc.ORCCM;
                        ls.CEP = lc.ORCEP;
                        ls.CNAE = lc.ORCNAE;
                        ls.CNPJ = lc.ORCNPJ;
                        ls.CODIGO = lc.ORCODIGO;
                        ls.COMPLEMENTO = lc.ORCOMPLEMENTO;
                        ls.DESCRICNAE = "";
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = "";
                        ls.ERRODESCRI = "";
                        ls.FANTASIA = lc.ORFANTASIA;
                        ls.FILIAL = lc.ORFILIAL;
                        ls.IBGE = lc.ORIBGE;
                        ls.IE = lc.ORIE;
                        ls.LOGRADOURO = lc.ORLOGRADOURO;
                        ls.MUNICIPIO = lc.ORMUNICIPIO;
                        ls.NUMERO = lc.ORNUM;
                        ls.QTDTENTATIVAS = "";
                        ls.RAZAO = lc.ORRAZAO;
                        ls.RFCODIGONJ = "";
                        ls.RFDATAABERTURA = "";
                        ls.RFDATACONSULTA = "";
                        ls.RFDATASITUACAO = "";
                        ls.RFDATASITUACAOESPECIAL = "";
                        ls.RFDESCRINJ = "";
                        ls.RFHORACONSULTA = "";
                        ls.RFMOTIVOSITUACAO = "";
                        ls.RFSITUACAOESPECIAL = "";
                        ls.RFTIPOEMPRESA = "";
                        ls.SICODINCRA = "";
                        ls.SIDATABAIXA = "";
                        ls.SIDATACONSULTA = "";
                        ls.SIDATAINCULSAO = "";
                        ls.SIEMAIL = "";
                        ls.SIEMISSAONFE = "";
                        ls.SIENQUADRAMENTOEMPRESA = "";
                        ls.SIMOTIVOBAIXA = "";
                        ls.SINUMEROCONSULTA = "";
                        ls.SIREGIMEAPURACAO = "";
                        ls.SIREGULARIDADEFISCAL = "";
                        ls.SITUACAOCNPJ = "";
                        ls.SITUACAOIE = "";
                        ls.SNSITUACAO = "";
                        ls.SNSITUACAOANTERIOR = "";
                        ls.SNSITUACAOSIMEI = "";
                        ls.SUFRAMA = lc.ORSUFRAMA;
                        ls.TIPOVERIFICACAO = "OR";
                        ls.UF = lc.ORUF;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        //RF
                        ls = new linhaSimple();

                        ls.BAIRRO = lc.RFBAIRRO;
                        ls.CCM = "";
                        ls.CEP = lc.RFCEP;
                        ls.CNAE = lc.RFCODIGOAE;
                        ls.CNPJ = lc.RFCNPJ;
                        ls.CODIGO = lc.ORCODIGO;
                        ls.COMPLEMENTO = lc.RFCOMPLEMENTO;
                        ls.DESCRICNAE = lc.RFDESCRICAOAE;
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = lc.RFERRCOD;
                        ls.ERRODESCRI = lc.RFERRDESCRI;
                        ls.FANTASIA = lc.RFNOMEFANTASIA;
                        ls.FILIAL = lc.ORFILIAL;
                        ls.IBGE = "";
                        ls.IE = "";
                        ls.LOGRADOURO = lc.RFLOGRADOURO;
                        ls.MUNICIPIO = lc.RFCIDADE;
                        ls.NUMERO = lc.RFNUMERO;
                        ls.QTDTENTATIVAS = "";
                        ls.RAZAO = lc.RFRAZAOSOCIAL;
                        ls.RFCODIGONJ = lc.RFCODIGONJ;
                        ls.RFDATAABERTURA = lc.RFDATAABERTURA;
                        ls.RFDATACONSULTA = lc.RFDATACONSULTA;
                        ls.RFDATASITUACAO = lc.RFDATASITUACAO;
                        ls.RFDATASITUACAOESPECIAL = lc.RFDATASITUACAOESPECIAL;
                        ls.RFDESCRINJ = lc.RFDESCRICAONJ;
                        ls.RFHORACONSULTA = lc.RFHORACONSULTA;
                        ls.RFMOTIVOSITUACAO = lc.RFMOTIVOSITUACAO;
                        ls.RFSITUACAOESPECIAL = lc.RFSITUACAOESPECIAL;
                        ls.RFTIPOEMPRESA = lc.RFTIPOEMPRESA;
                        ls.SICODINCRA = "";
                        ls.SIDATABAIXA = "";
                        ls.SIDATACONSULTA = "";
                        ls.SIDATAINCULSAO = "";
                        ls.SIEMAIL = "";
                        ls.SIEMISSAONFE = "";
                        ls.SIENQUADRAMENTOEMPRESA = "";
                        ls.SIMOTIVOBAIXA = "";
                        ls.SINUMEROCONSULTA = "";
                        ls.SIREGIMEAPURACAO = "";
                        ls.SIREGULARIDADEFISCAL = "";
                        ls.SITUACAOCNPJ = lc.RFSITUACAO;
                        ls.SITUACAOIE = "";
                        ls.SNSITUACAO = "";
                        ls.SNSITUACAOANTERIOR = "";
                        ls.SNSITUACAOSIMEI = "";
                        ls.SUFRAMA = lc.ORSUFRAMA;
                        ls.TIPOVERIFICACAO = "RF";
                        ls.UF = lc.RFESTADO;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        //IB
                        ls = new linhaSimple();

                        ls.BAIRRO = "";
                        ls.CCM = "";
                        ls.CEP = "";
                        ls.CNAE = "";
                        ls.CNPJ = lc.ORCNPJ;
                        ls.CODIGO = lc.ORCODIGO;
                        ls.COMPLEMENTO = "";
                        ls.DESCRICNAE = "";
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = "";
                        ls.ERRODESCRI = "";
                        ls.FANTASIA = "";
                        ls.FILIAL = lc.ORFILIAL;
                        ls.IBGE = lc.IBCODIBGE;
                        ls.IE = "";
                        ls.LOGRADOURO = "";
                        ls.MUNICIPIO = lc.RFCIDADE;
                        ls.NUMERO = "";
                        ls.QTDTENTATIVAS = "";
                        ls.RAZAO = "";
                        ls.RFCODIGONJ = "";
                        ls.RFDATAABERTURA = "";
                        ls.RFDATACONSULTA = "";
                        ls.RFDATASITUACAO = "";
                        ls.RFDATASITUACAOESPECIAL = "";
                        ls.RFDESCRINJ = "";
                        ls.RFHORACONSULTA = "";
                        ls.RFMOTIVOSITUACAO = "";
                        ls.RFSITUACAOESPECIAL = "";
                        ls.RFTIPOEMPRESA = "";
                        ls.SICODINCRA = "";
                        ls.SIDATABAIXA = "";
                        ls.SIDATACONSULTA = "";
                        ls.SIDATAINCULSAO = "";
                        ls.SIEMAIL = "";
                        ls.SIEMISSAONFE = "";
                        ls.SIENQUADRAMENTOEMPRESA = "";
                        ls.SIMOTIVOBAIXA = "";
                        ls.SINUMEROCONSULTA = "";
                        ls.SIREGIMEAPURACAO = "";
                        ls.SIREGULARIDADEFISCAL = "";
                        ls.SITUACAOCNPJ = "";
                        ls.SITUACAOIE = "";
                        ls.SNSITUACAO = "";
                        ls.SNSITUACAOANTERIOR = "";
                        ls.SNSITUACAOSIMEI = "";
                        ls.SUFRAMA = "";
                        ls.TIPOVERIFICACAO = "IB";
                        ls.UF = lc.RFESTADO;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        //SI
                        ls = new linhaSimple();

                        ls.BAIRRO = lc.SIBAIRRO;
                        ls.CCM = "";
                        ls.CEP = lc.SICEP;
                        ls.CNAE = "";
                        ls.CNPJ = lc.SICNPJ;
                        ls.CODIGO = lc.ORCODIGO;
                        ls.COMPLEMENTO = lc.SICOMPLEMENTO;
                        ls.DESCRICNAE = "";
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = lc.SIERRCOD;
                        ls.ERRODESCRI = lc.SIERRDESCRI;
                        ls.FANTASIA = lc.SINOMEFANTASIA;
                        ls.FILIAL = lc.ORFILIAL;
                        ls.IBGE = "";
                        ls.IE = lc.SIIE;
                        ls.LOGRADOURO = lc.SILOGRADOURO;
                        ls.MUNICIPIO = lc.SICIDADE;
                        ls.NUMERO = lc.SINUMERO;
                        ls.QTDTENTATIVAS = "";
                        ls.RAZAO = lc.SIRAZAOSOCIAL;
                        ls.RFCODIGONJ = "";
                        ls.RFDATAABERTURA = "";
                        ls.RFDATACONSULTA = "";
                        ls.RFDATASITUACAO = "";
                        ls.RFDATASITUACAOESPECIAL = "";
                        ls.RFDESCRINJ = "";
                        ls.RFHORACONSULTA = "";
                        ls.RFMOTIVOSITUACAO = "";
                        ls.RFSITUACAOESPECIAL = "";
                        ls.RFTIPOEMPRESA = "";
                        ls.SICODINCRA = lc.SICODINCRA;
                        ls.SIDATABAIXA = lc.SIDATABAIXA;
                        ls.SIDATACONSULTA = lc.SIDATACONSULTA;
                        ls.SIDATAINCULSAO = lc.SIDATAINCLUSAO;
                        ls.SIEMAIL = lc.SIEMAIL;
                        ls.SIEMISSAONFE = lc.SIEMISSAONFE;
                        ls.SIENQUADRAMENTOEMPRESA = lc.SIENQUADRAMENTOEMPRESA;
                        ls.SIMOTIVOBAIXA = lc.SIMOTIVOBAIXA;
                        ls.SINUMEROCONSULTA = lc.SINUMEROCONSULTA;
                        ls.SIREGIMEAPURACAO = lc.SIREGIMEAPURACAO;
                        ls.SIREGULARIDADEFISCAL = lc.SIREGULARIDADEFISCAL;
                        ls.SITUACAOCNPJ = "";
                        ls.SITUACAOIE = lc.SISITUACAO;
                        ls.SNSITUACAO = "";
                        ls.SNSITUACAOANTERIOR = "";
                        ls.SNSITUACAOSIMEI = "";
                        ls.SUFRAMA = "";
                        ls.TIPOVERIFICACAO = "SI";
                        ls.UF = lc.RFESTADO;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);



                    }
                    else
                    {
                        FW.cIO.appendLine(csvsaida, ls.CABECALHO);
                    }
                    count++;
                }
                sr.Dispose();
                sr = null;
                log.debug("Reg2Nreg Concluido");
            }
            catch (Exception ex)
            {
                log.erro("Reg2Nreg", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        private void Reg2NregPF()
        {
            try
            {
                int count = 0;
                string linha = "";

                cIO cio = new cIO();
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt");
                cio = null;

                linhaComplexPFV1 lc = new linhaComplexPFV1();
                linhaSimplePF ls = new linhaSimplePF();
                string csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
                string csvsaida = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";

                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csvfile, enc);
                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    //cio.appendLine("\"OR\";" + linha);

                    lc = new linhaComplexPFV1();

                    if (count != 0)
                    {
                        // OR
                        ls = new linhaSimplePF();
                        ls.inicia();
                        lc.autoCarga(linha);

                        ls.BAIRRO = lc.ORBAIRRO;
                        ls.CEP = lc.ORCEP;
                        ls.CPF = lc.ORCPF;
                        ls.NOME = lc.ORNOME;
                        ls.CODIGO = lc.ORCODIGO;
                        ls.COMPLEMENTO = lc.ORCOMPLEMENTO;
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = "";
                        ls.ERRODESCRI = "";
                        ls.FILIAL = lc.ORFILIAL;
                        ls.IBGE = lc.ORIBGE;
                        ls.IE = lc.ORIE;
                        ls.LOGRADOURO = lc.ORLOGRADOURO;
                        ls.MUNICIPIO = lc.ORMUNICIPIO;
                        ls.NUMERO = lc.ORNUM;
                        ls.QTDTENTATIVAS = "";
                        ls.RFDATAABERTURA = "";
                        ls.RFDATACONSULTA = "";
                        ls.RFDATASITUACAO = "";
                        ls.RFDATASITUACAOESPECIAL = "";
                        ls.RFHORACONSULTA = "";
                        ls.RFMOTIVOSITUACAO = "";
                        ls.RFSITUACAOESPECIAL = "";
                        ls.SIDATABAIXA = "";
                        ls.SIDATACONSULTA = "";
                        ls.SIDATAINCULSAO = "";
                        ls.SIEMAIL = "";
                        ls.SIMOTIVOBAIXA = "";
                        ls.SINUMEROCONSULTA = "";
                        ls.SIREGIMEAPURACAO = "";
                        ls.SIREGULARIDADEFISCAL = "";
                        ls.SITUACAOIE = "";
                        ls.TIPOVERIFICACAO = "OR";
                        ls.UF = lc.ORUF;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        //RF
                        ls = new linhaSimplePF();
                        ls.inicia();
                        ls.TIPOVERIFICACAO = "RF";
                        ls.CODIGO = lc.ORCODIGO;
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = lc.RFERRCOD;
                        ls.ERRODESCRI = lc.RFERRDESCRI;
                        ls.FILIAL = lc.ORFILIAL;
                        ls.NOME = lc.RFNOME;
                        ls.RFDATACONSULTA = lc.RFDATACONSULTA;
                        ls.SITUACAOCPF = lc.RFSITUACAO;
                        ls.RFHORACONSULTA = lc.RFCODIGOCONSULTA;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        //IB
                        ls = new linhaSimplePF();
                        ls.inicia();
                        ls.TIPOVERIFICACAO = "IB";
                        ls.CODIGO = lc.ORCODIGO;
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = lc.RFERRCOD;
                        ls.ERRODESCRI = lc.RFERRDESCRI;
                        ls.FILIAL = lc.ORFILIAL;
                        ls.IBGE = lc.IBCODIBGE;
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        //CO
                        ls = new linhaSimplePF();
                        ls.inicia();
                        ls.TIPOVERIFICACAO = "CO";
                        ls.BAIRRO = lc.COBAIRRO;
                        ls.CEP = lc.COCEP;
                        ls.CODIGO = lc.ORCODIGO;
                        ls.COMPLEMENTO = lc.COCOMPLEMENTO;
                        ls.EMPRESA = lc.OREMPRESA;
                        ls.ERROCOD = "";
                        ls.ERRODESCRI = "";
                        ls.FILIAL = lc.ORFILIAL;
                        ls.LOGRADOURO = lc.COLOGRADOURO;
                        ls.MUNICIPIO = lc.COCIDADE;
                        ls.NUMERO = lc.CONUMERO;
                        ls.UF = lc.COESTADO;
                        
                        FW.cIO.appendLine(csvsaida, ls.LINHA);

                        

                    }
                    else
                    {
                        FW.cIO.appendLine(csvsaida, ls.CABECALHO);
                    }
                    count++;
                }
                sr.Dispose();
                sr = null;
                log.debug("Reg2Nreg PF Concluido");
            }
            catch (Exception ex)
            {
                log.erro("Reg2Nreg PF", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        public void export2ExcelPJ()
        {
            linhaComplex lc = new linhaComplex();
            try
            {
                //System.Diagnostics.Debugger.Launch();

                string xlsfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls";
                string csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
                string csvregs = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";
                cIO cio = new cIO();
                cio.apagaArq(xlsfile);
                cio.copyFile(csrv.TEMPDIR + "/templatePJ.xls", xlsfile);

                

                FileStream fs = new FileStream(csrv.TEMPDIR + "/templatePJ.xls", FileMode.Open, FileAccess.Read);
                FileStream msResult = new FileStream(xlsfile, FileMode.Create);

                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);



                var style1 = templateWorkbook.CreateCellStyle();
                style1.FillBackgroundColor = HSSFColor.WHITE.index;
                style1.FillForegroundColor = HSSFColor.WHITE.index;
                style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
                style1.BorderBottom = CellBorderType.THIN;
                style1.BorderLeft = CellBorderType.THIN;
                style1.BorderRight = CellBorderType.THIN;
                style1.BorderTop = CellBorderType.THIN;
                style1.BottomBorderColor = HSSFColor.BLACK.index;
                style1.LeftBorderColor = HSSFColor.BLACK.index;
                style1.RightBorderColor = HSSFColor.BLACK.index;
                style1.TopBorderColor = HSSFColor.BLACK.index;

                var styleNormal = templateWorkbook.CreateCellStyle();
                styleNormal.FillBackgroundColor = HSSFColor.WHITE.index;
                styleNormal.FillForegroundColor = HSSFColor.WHITE.index;
                styleNormal.FillPattern = FillPatternType.SOLID_FOREGROUND;
                styleNormal.BorderBottom = CellBorderType.THIN;
                styleNormal.BorderLeft = CellBorderType.THIN;
                styleNormal.BorderRight = CellBorderType.THIN;
                styleNormal.BorderTop = CellBorderType.THIN;
                styleNormal.BottomBorderColor = HSSFColor.BLACK.index;
                styleNormal.LeftBorderColor = HSSFColor.BLACK.index;
                styleNormal.RightBorderColor = HSSFColor.BLACK.index;
                styleNormal.TopBorderColor = HSSFColor.BLACK.index;

                var styleAtencao = templateWorkbook.CreateCellStyle();
                styleAtencao.FillBackgroundColor = HSSFColor.YELLOW.index;
                styleAtencao.FillForegroundColor = HSSFColor.YELLOW.index;
                styleAtencao.FillPattern = FillPatternType.SOLID_FOREGROUND;
                styleAtencao.BorderBottom = CellBorderType.THIN;
                styleAtencao.BorderLeft = CellBorderType.THIN;
                styleAtencao.BorderRight = CellBorderType.THIN;
                styleAtencao.BorderTop = CellBorderType.THIN;
                styleAtencao.BottomBorderColor = HSSFColor.BLACK.index;
                styleAtencao.LeftBorderColor = HSSFColor.BLACK.index;
                styleAtencao.RightBorderColor = HSSFColor.BLACK.index;
                styleAtencao.TopBorderColor = HSSFColor.BLACK.index;

                var styleCritico = templateWorkbook.CreateCellStyle();
                styleCritico.FillBackgroundColor = HSSFColor.RED.index;
                styleCritico.FillForegroundColor = HSSFColor.RED.index;
                styleCritico.FillPattern = FillPatternType.SOLID_FOREGROUND;
                styleCritico.BorderBottom = CellBorderType.THIN;
                styleCritico.BorderLeft = CellBorderType.THIN;
                styleCritico.BorderRight = CellBorderType.THIN;
                styleCritico.BorderTop = CellBorderType.THIN;
                styleCritico.BottomBorderColor = HSSFColor.BLACK.index;
                styleCritico.LeftBorderColor = HSSFColor.BLACK.index;
                styleCritico.RightBorderColor = HSSFColor.BLACK.index;
                styleCritico.TopBorderColor = HSSFColor.BLACK.index;





                // consultas
                var sheet = templateWorkbook.GetSheet("Consultas_CNPJ");
                int count = 0;
                int pag = 0;
                string linha = "";
                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csvregs, enc);

                //while (sr.Peek() >= 0)
                //{
                //    linha = sr.ReadLine();
                //    List<string> campos = linha2List(linha);
                //    int cols = 0;

                //    if (count > 0)
                //    {
                //        var row = sheet.CreateRow(count);
                //        //foreach (string s in campos)
                //        //{
                //        //    row.CreateCell(cols).SetCellValue(s);
                //        //    row.GetCell(cols).CellStyle = styleNormal;
                //        //    cols++;
                //        //}

                //        int cc = campos.Count;
                //        for (int i = 0; i < cc - 1; i++)
                //        {
                //            row.CreateCell(cols).SetCellValue(campos[i]);
                //            row.GetCell(cols).CellStyle = styleNormal;
                //            cols++;
                //        }

                //    }
                //    if (count > 65000)
                //    { 
                //        pag ++;
                //        sheet = templateWorkbook.CreateSheet("Consultas_CNPJ_" + pag.ToString());
                //        count = 1;
                //    }
                //    count++;

                //}
                //log.debug("gravou xls pasta consultas");


                //Original
                sheet = templateWorkbook.GetSheet("Origem_CNPJ");
                //csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";

                count = 0;
                linha = "";
                sr = new StreamReader(csvfile, enc);


                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    if (count > 0)
                    {
                        lc.autoCarga(linha);
                        var row = sheet.CreateRow(count);
                        row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                        row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                        row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                        row.CreateCell(3).SetCellValue(lc.ORCNPJ);
                        row.CreateCell(4).SetCellValue(lc.ORRAZAO);
                        row.CreateCell(5).SetCellValue(lc.ORFANTASIA);
                        row.CreateCell(6).SetCellValue(lc.ORLOGRADOURO);
                        row.CreateCell(7).SetCellValue(lc.ORNUM);
                        row.CreateCell(8).SetCellValue(lc.ORCOMPLEMENTO);
                        row.CreateCell(9).SetCellValue(lc.ORBAIRRO);
                        row.CreateCell(10).SetCellValue(lc.ORMUNICIPIO);
                        row.CreateCell(11).SetCellValue(lc.ORUF);
                        row.CreateCell(12).SetCellValue(lc.ORCEP);
                        row.CreateCell(13).SetCellValue(lc.ORIE);
                        row.CreateCell(14).SetCellValue(lc.ORIBGE);
                        row.CreateCell(15).SetCellValue(lc.ORCCM);
                        row.CreateCell(16).SetCellValue(lc.ORCNAE);
                        row.CreateCell(17).SetCellValue(lc.ORSUFRAMA);


                        for (int ii = 0; ii < 18; ii++)
                        {
                            row.GetCell(ii).CellStyle = styleNormal;
                        }

                        //foreach (string s in campos)
                        //{
                        //    row.CreateCell(cols).SetCellValue(s);
                        //    row.GetCell(cols).CellStyle = styleNormal;
                        //    cols++;
                        //}
                    }
                    count++;

                }
                log.debug("gravou xls pasta original");

                //Linha complexa
                sheet = templateWorkbook.GetSheet("Saneamento_CNPJ");
                //csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";

                count = 0;
                linha = "";
                sr = new StreamReader(csvfile, enc);


                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    //List<string> campos = linha2List(linha);
                    lc = new linhaComplex();
                    lc.autoCarga(linha);
                    //int cols = 0;
                    if (count > 0)
                    {
                        var row = sheet.CreateRow(count);

                        row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                        row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                        row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                        row.CreateCell(3).SetCellValue(lc.ORCNPJ);
                        row.CreateCell(4).SetCellValue(lc.ORRAZAO);
                        row.CreateCell(5).SetCellValue(lc.ORFANTASIA);
                        row.CreateCell(6).SetCellValue(lc.ORLOGRADOURO);
                        row.CreateCell(7).SetCellValue(lc.ORNUM);
                        row.CreateCell(8).SetCellValue(lc.ORCOMPLEMENTO);
                        row.CreateCell(9).SetCellValue(lc.ORBAIRRO);
                        row.CreateCell(10).SetCellValue(lc.ORMUNICIPIO);
                        row.CreateCell(11).SetCellValue(lc.ORUF);
                        row.CreateCell(12).SetCellValue(lc.ORCEP);
                        row.CreateCell(13).SetCellValue(lc.ORIE);
                        row.CreateCell(14).SetCellValue(lc.ORIBGE);
                        row.CreateCell(15).SetCellValue(lc.ORCCM);
                        row.CreateCell(16).SetCellValue(lc.ORCNAE);
                        row.CreateCell(17).SetCellValue(lc.ORSUFRAMA);
                        row.CreateCell(18).SetCellValue(lc.ORRES0);
                        row.CreateCell(19).SetCellValue(lc.ORRES1);
                        row.CreateCell(20).SetCellValue(lc.ORRES2);
                        row.CreateCell(21).SetCellValue(lc.ORRES3);
                        row.CreateCell(22).SetCellValue(lc.ORRES4);
                        row.CreateCell(23).SetCellValue(lc.ORRES5);
                        row.CreateCell(24).SetCellValue(lc.ORRES6);
                        row.CreateCell(25).SetCellValue(lc.ORRES7);
                        row.CreateCell(26).SetCellValue(lc.ORRES8);
                        row.CreateCell(27).SetCellValue(lc.ORRES9);
                        row.CreateCell(28).SetCellValue(lc.IBCODIBGE);
                        row.CreateCell(29).SetCellValue(lc.RFCNPJ);
                        row.CreateCell(30).SetCellValue(lc.RFDATAABERTURA);
                        row.CreateCell(31).SetCellValue(lc.RFRAZAOSOCIAL);
                        row.CreateCell(32).SetCellValue(lc.RFNOMEFANTASIA);
                        row.CreateCell(33).SetCellValue(lc.RFCODIGOAE);
                        row.CreateCell(34).SetCellValue(lc.RFDESCRICAOAE);
                        row.CreateCell(35).SetCellValue(lc.RFCODIGONJ);
                        row.CreateCell(36).SetCellValue(lc.RFDESCRICAONJ);
                        row.CreateCell(37).SetCellValue(lc.RFCODIGOAESECUNDARIA);
                        row.CreateCell(38).SetCellValue(lc.RFDESCRICAOAESECUNDARIA);
                        row.CreateCell(39).SetCellValue(lc.RFLOGRADOURO);
                        row.CreateCell(40).SetCellValue(lc.RFNUMERO);
                        row.CreateCell(41).SetCellValue(lc.RFCOMPLEMENTO);
                        row.CreateCell(42).SetCellValue(lc.RFCEP);
                        row.CreateCell(43).SetCellValue(lc.RFBAIRRO);
                        row.CreateCell(44).SetCellValue(lc.RFCIDADE);
                        row.CreateCell(45).SetCellValue(lc.RFESTADO);
                        row.CreateCell(46).SetCellValue(lc.RFSITUACAO);
                        row.CreateCell(47).SetCellValue(lc.RFDATASITUACAO);
                        row.CreateCell(48).SetCellValue(lc.RFSITUACAOESPECIAL);
                        row.CreateCell(49).SetCellValue(lc.RFDATASITUACAOESPECIAL);
                        row.CreateCell(50).SetCellValue(lc.RFDATACONSULTA);
                        row.CreateCell(51).SetCellValue(lc.RFHORACONSULTA);
                        row.CreateCell(52).SetCellValue(lc.RFTIPOEMPRESA);
                        row.CreateCell(53).SetCellValue(lc.RFMOTIVOSITUACAO);
                        row.CreateCell(54).SetCellValue(lc.RFERRCOD);
                        row.CreateCell(55).SetCellValue(lc.RFERRDESCRI);
                        row.CreateCell(56).SetCellValue(lc.RFRES0);
                        row.CreateCell(57).SetCellValue(lc.RFRES1);
                        row.CreateCell(58).SetCellValue(lc.RFRES2);
                        row.CreateCell(59).SetCellValue(lc.RFRES3);
                        row.CreateCell(60).SetCellValue(lc.RFRES4);
                        row.CreateCell(61).SetCellValue(lc.RFRES5);
                        row.CreateCell(62).SetCellValue(lc.RFRES6);
                        row.CreateCell(63).SetCellValue(lc.RFRES7);
                        row.CreateCell(64).SetCellValue(lc.RFRES8);
                        row.CreateCell(65).SetCellValue(lc.RFRES9);
                        row.CreateCell(66).SetCellValue(lc.SICNPJ);
                        row.CreateCell(67).SetCellValue(lc.SIIE);
                        row.CreateCell(68).SetCellValue(lc.SIRAZAOSOCIAL);
                        row.CreateCell(69).SetCellValue(lc.SINOMEFANTASIA);
                        row.CreateCell(70).SetCellValue(lc.SILOGRADOURO);
                        row.CreateCell(71).SetCellValue(lc.SINUMERO);
                        row.CreateCell(72).SetCellValue(lc.SICOMPLEMENTO);
                        row.CreateCell(73).SetCellValue(lc.SIBAIRRO);
                        row.CreateCell(74).SetCellValue(lc.SICIDADE);
                        row.CreateCell(75).SetCellValue(lc.SIESTADO);
                        row.CreateCell(76).SetCellValue(lc.SICEP);
                        row.CreateCell(77).SetCellValue(lc.SIEMAIL);
                        row.CreateCell(78).SetCellValue(lc.SISITUACAO);
                        row.CreateCell(79).SetCellValue(lc.SIDATAINCLUSAO);
                        row.CreateCell(80).SetCellValue(lc.SIDATABAIXA);
                        row.CreateCell(81).SetCellValue(lc.SIDATACONSULTA);
                        row.CreateCell(82).SetCellValue(lc.SINUMEROCONSULTA);
                        row.CreateCell(83).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                        row.CreateCell(84).SetCellValue(lc.SIEMISSAONFE);
                        row.CreateCell(85).SetCellValue(lc.SICODINCRA);
                        row.CreateCell(86).SetCellValue(lc.SIREGIMEAPURACAO);
                        row.CreateCell(87).SetCellValue(lc.SIMOTIVOBAIXA);
                        row.CreateCell(88).SetCellValue(lc.SIREGULARIDADEFISCAL);
                        row.CreateCell(89).SetCellValue(lc.SIERRCOD);
                        row.CreateCell(90).SetCellValue(lc.SIERRDESCRI);
                        row.CreateCell(91).SetCellValue(lc.SIRES0);
                        row.CreateCell(92).SetCellValue(lc.SIRES1);
                        row.CreateCell(93).SetCellValue(lc.SIRES2);
                        row.CreateCell(94).SetCellValue(lc.SIRES3);
                        row.CreateCell(95).SetCellValue(lc.SIRES4);
                        row.CreateCell(96).SetCellValue(lc.SIRES5);
                        row.CreateCell(97).SetCellValue(lc.SIRES6);
                        row.CreateCell(98).SetCellValue(lc.SIRES7);
                        row.CreateCell(99).SetCellValue(lc.SIRES8);
                        row.CreateCell(100).SetCellValue(lc.SIRES9);
                        row.CreateCell(101).SetCellValue(lc.SNCNPJ);
                        row.CreateCell(102).SetCellValue(lc.SNRAZAOSOCIAL);
                        row.CreateCell(103).SetCellValue(lc.SNSITUACAO);
                        row.CreateCell(104).SetCellValue(lc.SNDATASITUACAO);
                        row.CreateCell(105).SetCellValue(lc.SNSITUACAOSIMEI);
                        row.CreateCell(106).SetCellValue(lc.SNSITUACAOANTERIOR);
                        row.CreateCell(107).SetCellValue(lc.SNERRCOD);
                        row.CreateCell(108).SetCellValue(lc.SNERRDESCRI);
                        row.CreateCell(109).SetCellValue(lc.SNRES0);
                        row.CreateCell(110).SetCellValue(lc.SNRES1);
                        row.CreateCell(111).SetCellValue(lc.SNRES2);
                        row.CreateCell(112).SetCellValue(lc.SNRES3);
                        row.CreateCell(113).SetCellValue(lc.SNRES4);
                        row.CreateCell(114).SetCellValue(lc.SUCNPJ);
                        row.CreateCell(115).SetCellValue(lc.SUSUFRAMANUM);
                        row.CreateCell(116).SetCellValue(lc.SURAZAOSOCIAL);
                        row.CreateCell(117).SetCellValue(lc.SULOGRADOURO);
                        row.CreateCell(118).SetCellValue(lc.SUNUMERO);
                        row.CreateCell(119).SetCellValue(lc.SUCOMPLEMENTO);
                        row.CreateCell(120).SetCellValue(lc.SUBAIRRO);
                        row.CreateCell(121).SetCellValue(lc.SUESTADO);
                        row.CreateCell(122).SetCellValue(lc.SUCIDADE);
                        row.CreateCell(123).SetCellValue(lc.SUCEP);
                        row.CreateCell(124).SetCellValue(lc.SUTELEFONE);
                        row.CreateCell(125).SetCellValue(lc.SUSITUACAO);
                        row.CreateCell(126).SetCellValue(lc.SUDATACONSULTA);
                        row.CreateCell(127).SetCellValue(lc.SUATIVIDADEECONOMICA);
                        row.CreateCell(128).SetCellValue(lc.SUDATAVALIDADE);
                        row.CreateCell(129).SetCellValue(lc.SUTIPOINCENTIVO);
                        row.CreateCell(130).SetCellValue(lc.SUEMAIL);
                        row.CreateCell(131).SetCellValue(lc.SUPAGINA);
                        row.CreateCell(132).SetCellValue(lc.SUERRCOD);
                        row.CreateCell(133).SetCellValue(lc.SUERRDESCRI);
                        row.CreateCell(134).SetCellValue(lc.SURES0);
                        row.CreateCell(135).SetCellValue(lc.SURES1);
                        row.CreateCell(136).SetCellValue(lc.SURES2);
                        row.CreateCell(137).SetCellValue(lc.SURES3);
                        row.CreateCell(138).SetCellValue(lc.SURES4);
                        row.CreateCell(139).SetCellValue(lc.SURES5);
                        row.CreateCell(140).SetCellValue(lc.SURES6);
                        row.CreateCell(141).SetCellValue(lc.SURES7);
                        row.CreateCell(142).SetCellValue(lc.SURES8);
                        row.CreateCell(143).SetCellValue(lc.SURES9);


                        for (int ii = 0; ii <= 143; ii++)
                        {
                            row.GetCell(ii).CellStyle = styleNormal;
                        }

                        //if (lc.STATUS == "1")
                        //{ row.GetCell(3).CellStyle = styleAtencao; }
                        //else
                        //{ row.GetCell(3).CellStyle = styleCritico; }

                        // pinta por qtd de comparações
                        try
                        {
                            int _stts = Convert.ToInt32(lc.STATUS);
                           // if (_stts >= 2) row.GetCell(0).CellStyle = styleCritico;
                           // if (_stts == 1) row.GetCell(0).CellStyle = styleAtencao;
                        }
                        catch (Exception ex)
                        {

                        }

                        //if (lc.STATUS == "0") 
                        row.GetCell(0).CellStyle = styleNormal;

                        if (lc.DIFCNPJ == "2")
                        {
                            row.GetCell(3).CellStyle = styleCritico;
                            row.GetCell(29).CellStyle = styleCritico;
                            row.GetCell(46).CellStyle = styleCritico;
                        }
                        if (lc.DIFCNPJ == "1") row.GetCell(3).CellStyle = styleAtencao;
                        //if (lc.DIFCNPJ == "0") row.GetCell(4).CellStyle = styleNormal;
                        if (lc.DIFRAZAO == "1")
                        {
                            row.GetCell(4).CellStyle = styleAtencao;
                            row.GetCell(31).CellStyle = styleAtencao;
                        }
                        if (lc.DIFFANTASIA == "1")
                        {
                            row.GetCell(5).CellStyle = styleAtencao;
                            row.GetCell(32).CellStyle = styleAtencao;
                        }

                        if (lc.DIFLOGRADOURO == "1")
                        {
                            row.GetCell(6).CellStyle = styleAtencao;
                            row.GetCell(39).CellStyle = styleAtencao;
                        }
                        if (lc.DIFNUM == "1")
                        {
                            row.GetCell(7).CellStyle = styleAtencao;
                            row.GetCell(40).CellStyle = styleAtencao;
                        }

                        if (lc.DIFCOMPLEMENTO == "1")
                        {
                            row.GetCell(8).CellStyle = styleAtencao;
                            row.GetCell(41).CellStyle = styleAtencao;
                        }

                        if (lc.DIFBAIRRO == "1")
                        {
                            row.GetCell(9).CellStyle = styleAtencao;
                            row.GetCell(43).CellStyle = styleAtencao;
                        }

                        if (lc.DIFMUNICIPIO == "1")
                        {
                            row.GetCell(10).CellStyle = styleAtencao;
                            row.GetCell(44).CellStyle = styleAtencao;
                        }
                        if (lc.DIFUF == "1")
                        {
                            row.GetCell(11).CellStyle = styleAtencao;
                            row.GetCell(45).CellStyle = styleAtencao;
                        }

                        if (lc.DIFIBGE == "1")
                        {
                            row.GetCell(14).CellStyle = styleAtencao;
                            row.GetCell(28).CellStyle = styleAtencao;
                        }

                        if (lc.DIFCEP == "1")
                        {
                            row.GetCell(12).CellStyle = styleAtencao;
                            row.GetCell(42).CellStyle = styleAtencao;
                        }
                        if (lc.DIFIE == "1")
                        {
                            row.GetCell(13).CellStyle = styleAtencao;
                            row.GetCell(67).CellStyle = styleAtencao;
                        }
                        if (lc.DIFIE == "2")
                        {
                            row.GetCell(13).CellStyle = styleCritico;
                            row.GetCell(67).CellStyle = styleCritico;
                            row.GetCell(78).CellStyle = styleCritico;
                        }



                        //foreach (string s in campos)
                        //{
                        //    row.CreateCell(cols).SetCellValue(s);
                        //    cols++;
                        //}
                    }
                    count++;

                }
                log.debug("gravou xls pasta saneamento cnpj");


                // Sugestao Update
                // 0 CODIGO	1 EMPRESA	2 FILIAL	3 CNPJ	4 SITUACAOCNPJ	5 IE	6 SITUACAOIE	7 RAZAO	8 FANTASIA	9 LOGRADOURO	10 NUMERO	
                // 11 COMPLEMENTO	12 BAIRRO	13 MUNICIPIO	14 UF	15 CEP	16 IBGE	17 CCM	18 CNAE	19 SUFRAMA

                sheet = templateWorkbook.GetSheet("SUGESTAO_UPDATE_CNPJ");
                var sheetRes = templateWorkbook.GetSheet("Restricoes_CNPJ_IE");
                var sheetMie = templateWorkbook.GetSheet("Multiplos_IE");

               // csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";

                count = 0;
                int countRes = 0;
                int countMie = 0;
                linha = "";
                sr = new StreamReader(csvfile, enc);


                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    //List<string> campos = linha2List(linha);
                    lc = new linhaComplex();
                    lc.autoCarga(linha);
                    //int cols = 0;
                    if (count > 0)
                    {
                        if (lc.DIFCNPJ.Trim() == "")
                            lc.DIFCNPJ = "2";

                        if (Convert.ToInt16(lc.DIFCNPJ) >= 2 || Convert.ToInt16(lc.DIFIE) >= 2 || lc.DIFMIE == "1")
                        {
                            // poe em tabela separada

                            if (lc.DIFMIE == "0")
                            {
                                //Restricoes_CNPJ_IE
                                countRes++;
                                var row = sheetRes.CreateRow(countRes);
                                //var row = sheet.CreateRow(count);

                                row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                                row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                                row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                                row.CreateCell(3).SetCellValue(lc.ORCNPJ);
                                row.CreateCell(4).SetCellValue(lc.ORRAZAO);
                                row.CreateCell(5).SetCellValue(lc.ORFANTASIA);
                                row.CreateCell(6).SetCellValue(lc.ORLOGRADOURO);
                                row.CreateCell(7).SetCellValue(lc.ORNUM);
                                row.CreateCell(8).SetCellValue(lc.ORCOMPLEMENTO);
                                row.CreateCell(9).SetCellValue(lc.ORBAIRRO);
                                row.CreateCell(10).SetCellValue(lc.ORMUNICIPIO);
                                row.CreateCell(11).SetCellValue(lc.ORUF);
                                row.CreateCell(12).SetCellValue(lc.ORCEP);
                                row.CreateCell(13).SetCellValue(lc.ORIE);
                                row.CreateCell(14).SetCellValue(lc.ORIBGE);
                                row.CreateCell(15).SetCellValue(lc.ORCCM);
                                row.CreateCell(16).SetCellValue(lc.ORCNAE);
                                row.CreateCell(17).SetCellValue(lc.ORSUFRAMA);
                                row.CreateCell(18).SetCellValue(lc.ORRES0);
                                row.CreateCell(19).SetCellValue(lc.ORRES1);
                                row.CreateCell(20).SetCellValue(lc.ORRES2);
                                row.CreateCell(21).SetCellValue(lc.ORRES3);
                                row.CreateCell(22).SetCellValue(lc.ORRES4);
                                row.CreateCell(23).SetCellValue(lc.ORRES5);
                                row.CreateCell(24).SetCellValue(lc.ORRES6);
                                row.CreateCell(25).SetCellValue(lc.ORRES7);
                                row.CreateCell(26).SetCellValue(lc.ORRES8);
                                row.CreateCell(27).SetCellValue(lc.ORRES9);
                                row.CreateCell(28).SetCellValue(lc.IBCODIBGE);
                                row.CreateCell(29).SetCellValue(lc.RFCNPJ);
                                row.CreateCell(30).SetCellValue(lc.RFDATAABERTURA);
                                row.CreateCell(31).SetCellValue(lc.RFRAZAOSOCIAL);
                                row.CreateCell(32).SetCellValue(lc.RFNOMEFANTASIA);
                                row.CreateCell(33).SetCellValue(lc.RFCODIGOAE);
                                row.CreateCell(34).SetCellValue(lc.RFDESCRICAOAE);
                                row.CreateCell(35).SetCellValue(lc.RFCODIGONJ);
                                row.CreateCell(36).SetCellValue(lc.RFDESCRICAONJ);
                                row.CreateCell(37).SetCellValue(lc.RFCODIGOAESECUNDARIA);
                                row.CreateCell(38).SetCellValue(lc.RFDESCRICAOAESECUNDARIA);
                                row.CreateCell(39).SetCellValue(lc.RFLOGRADOURO);
                                row.CreateCell(40).SetCellValue(lc.RFNUMERO);
                                row.CreateCell(41).SetCellValue(lc.RFCOMPLEMENTO);
                                row.CreateCell(42).SetCellValue(lc.RFCEP);
                                row.CreateCell(43).SetCellValue(lc.RFBAIRRO);
                                row.CreateCell(44).SetCellValue(lc.RFCIDADE);
                                row.CreateCell(45).SetCellValue(lc.RFESTADO);
                                row.CreateCell(46).SetCellValue(lc.RFSITUACAO);
                                row.CreateCell(47).SetCellValue(lc.RFDATASITUACAO);
                                row.CreateCell(48).SetCellValue(lc.RFSITUACAOESPECIAL);
                                row.CreateCell(49).SetCellValue(lc.RFDATASITUACAOESPECIAL);
                                row.CreateCell(50).SetCellValue(lc.RFDATACONSULTA);
                                row.CreateCell(51).SetCellValue(lc.RFHORACONSULTA);
                                row.CreateCell(52).SetCellValue(lc.RFTIPOEMPRESA);
                                row.CreateCell(53).SetCellValue(lc.RFMOTIVOSITUACAO);
                                row.CreateCell(54).SetCellValue(lc.RFERRCOD);
                                row.CreateCell(55).SetCellValue(lc.RFERRDESCRI);
                                row.CreateCell(56).SetCellValue(lc.RFRES0);
                                row.CreateCell(57).SetCellValue(lc.RFRES1);
                                row.CreateCell(58).SetCellValue(lc.RFRES2);
                                row.CreateCell(59).SetCellValue(lc.RFRES3);
                                row.CreateCell(60).SetCellValue(lc.RFRES4);
                                row.CreateCell(61).SetCellValue(lc.RFRES5);
                                row.CreateCell(62).SetCellValue(lc.RFRES6);
                                row.CreateCell(63).SetCellValue(lc.RFRES7);
                                row.CreateCell(64).SetCellValue(lc.RFRES8);
                                row.CreateCell(65).SetCellValue(lc.RFRES9);
                                row.CreateCell(66).SetCellValue(lc.SICNPJ);
                                row.CreateCell(67).SetCellValue(lc.SIIE);
                                row.CreateCell(68).SetCellValue(lc.SIRAZAOSOCIAL);
                                row.CreateCell(69).SetCellValue(lc.SINOMEFANTASIA);
                                row.CreateCell(70).SetCellValue(lc.SILOGRADOURO);
                                row.CreateCell(71).SetCellValue(lc.SINUMERO);
                                row.CreateCell(72).SetCellValue(lc.SICOMPLEMENTO);
                                row.CreateCell(73).SetCellValue(lc.SIBAIRRO);
                                row.CreateCell(74).SetCellValue(lc.SICIDADE);
                                row.CreateCell(75).SetCellValue(lc.SIESTADO);
                                row.CreateCell(76).SetCellValue(lc.SICEP);
                                row.CreateCell(77).SetCellValue(lc.SIEMAIL);
                                row.CreateCell(78).SetCellValue(lc.SISITUACAO);
                                row.CreateCell(79).SetCellValue(lc.SIDATAINCLUSAO);
                                row.CreateCell(80).SetCellValue(lc.SIDATABAIXA);
                                row.CreateCell(81).SetCellValue(lc.SIDATACONSULTA);
                                row.CreateCell(82).SetCellValue(lc.SINUMEROCONSULTA);
                                row.CreateCell(83).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                                row.CreateCell(84).SetCellValue(lc.SIEMISSAONFE);
                                row.CreateCell(85).SetCellValue(lc.SICODINCRA);
                                row.CreateCell(86).SetCellValue(lc.SIREGIMEAPURACAO);
                                row.CreateCell(87).SetCellValue(lc.SIMOTIVOBAIXA);
                                row.CreateCell(88).SetCellValue(lc.SIREGULARIDADEFISCAL);
                                row.CreateCell(89).SetCellValue(lc.SIERRCOD);
                                row.CreateCell(90).SetCellValue(lc.SIERRDESCRI);
                                row.CreateCell(91).SetCellValue(lc.SIRES0);
                                row.CreateCell(92).SetCellValue(lc.SIRES1);
                                row.CreateCell(93).SetCellValue(lc.SIRES2);
                                row.CreateCell(94).SetCellValue(lc.SIRES3);
                                row.CreateCell(95).SetCellValue(lc.SIRES4);
                                row.CreateCell(96).SetCellValue(lc.SIRES5);
                                row.CreateCell(97).SetCellValue(lc.SIRES6);
                                row.CreateCell(98).SetCellValue(lc.SIRES7);
                                row.CreateCell(99).SetCellValue(lc.SIRES8);
                                row.CreateCell(100).SetCellValue(lc.SIRES9);
                                row.CreateCell(101).SetCellValue(lc.SNCNPJ);
                                row.CreateCell(102).SetCellValue(lc.SNRAZAOSOCIAL);
                                row.CreateCell(103).SetCellValue(lc.SNSITUACAO);
                                row.CreateCell(104).SetCellValue(lc.SNDATASITUACAO);
                                row.CreateCell(105).SetCellValue(lc.SNSITUACAOSIMEI);
                                row.CreateCell(106).SetCellValue(lc.SNSITUACAOANTERIOR);
                                row.CreateCell(107).SetCellValue(lc.SNERRCOD);
                                row.CreateCell(108).SetCellValue(lc.SNERRDESCRI);
                                row.CreateCell(109).SetCellValue(lc.SNRES0);
                                row.CreateCell(110).SetCellValue(lc.SNRES1);
                                row.CreateCell(111).SetCellValue(lc.SNRES2);
                                row.CreateCell(112).SetCellValue(lc.SNRES3);
                                row.CreateCell(113).SetCellValue(lc.SNRES4);
                                row.CreateCell(114).SetCellValue(lc.SUCNPJ);
                                row.CreateCell(115).SetCellValue(lc.SUSUFRAMANUM);
                                row.CreateCell(116).SetCellValue(lc.SURAZAOSOCIAL);
                                row.CreateCell(117).SetCellValue(lc.SULOGRADOURO);
                                row.CreateCell(118).SetCellValue(lc.SUNUMERO);
                                row.CreateCell(119).SetCellValue(lc.SUCOMPLEMENTO);
                                row.CreateCell(120).SetCellValue(lc.SUBAIRRO);
                                row.CreateCell(121).SetCellValue(lc.SUESTADO);
                                row.CreateCell(122).SetCellValue(lc.SUCIDADE);
                                row.CreateCell(123).SetCellValue(lc.SUCEP);
                                row.CreateCell(124).SetCellValue(lc.SUTELEFONE);
                                row.CreateCell(125).SetCellValue(lc.SUSITUACAO);
                                row.CreateCell(126).SetCellValue(lc.SUDATACONSULTA);
                                row.CreateCell(127).SetCellValue(lc.SUATIVIDADEECONOMICA);
                                row.CreateCell(128).SetCellValue(lc.SUDATAVALIDADE);
                                row.CreateCell(129).SetCellValue(lc.SUTIPOINCENTIVO);
                                row.CreateCell(130).SetCellValue(lc.SUEMAIL);
                                row.CreateCell(131).SetCellValue(lc.SUPAGINA);
                                row.CreateCell(132).SetCellValue(lc.SUERRCOD);
                                row.CreateCell(133).SetCellValue(lc.SUERRDESCRI);
                                row.CreateCell(134).SetCellValue(lc.SURES0);
                                row.CreateCell(135).SetCellValue(lc.SURES1);
                                row.CreateCell(136).SetCellValue(lc.SURES2);
                                row.CreateCell(137).SetCellValue(lc.SURES3);
                                row.CreateCell(138).SetCellValue(lc.SURES4);
                                row.CreateCell(139).SetCellValue(lc.SURES5);
                                row.CreateCell(140).SetCellValue(lc.SURES6);
                                row.CreateCell(141).SetCellValue(lc.SURES7);
                                row.CreateCell(142).SetCellValue(lc.SURES8);
                                row.CreateCell(143).SetCellValue(lc.SURES9);


                                for (int ii = 0; ii <= 143; ii++)
                                {
                                    row.GetCell(ii).CellStyle = styleNormal;
                                }

                                //if (lc.STATUS == "1")
                                //{ row.GetCell(3).CellStyle = styleAtencao; }
                                //else
                                //{ row.GetCell(3).CellStyle = styleCritico; }

                                // pinta por qtd de comparações
                                try
                                {
                                    int _stts = Convert.ToInt32(lc.STATUS);
                                   // if (_stts >= 2) row.GetCell(0).CellStyle = styleCritico;
                                   // if (_stts == 1) row.GetCell(0).CellStyle = styleAtencao;
                                }
                                catch (Exception ex)
                                {

                                }

                              //  if (lc.STATUS == "0") 
                                    row.GetCell(0).CellStyle = styleNormal;

                                if (lc.DIFCNPJ == "2")
                                {
                                    row.GetCell(3).CellStyle = styleCritico;
                                    row.GetCell(29).CellStyle = styleCritico;
                                    row.GetCell(46).CellStyle = styleCritico;
                                }
                                if (lc.DIFCNPJ == "1") row.GetCell(3).CellStyle = styleAtencao;
                                //if (lc.DIFCNPJ == "0") row.GetCell(4).CellStyle = styleNormal;
                                if (lc.DIFRAZAO == "1")
                                {
                                    row.GetCell(4).CellStyle = styleAtencao;
                                    row.GetCell(31).CellStyle = styleAtencao;
                                }
                                if (lc.DIFFANTASIA == "1")
                                {
                                    row.GetCell(5).CellStyle = styleAtencao;
                                    row.GetCell(32).CellStyle = styleAtencao;
                                }

                                if (lc.DIFLOGRADOURO == "1")
                                {
                                    row.GetCell(6).CellStyle = styleAtencao;
                                    row.GetCell(39).CellStyle = styleAtencao;
                                }
                                if (lc.DIFNUM == "1")
                                {
                                    row.GetCell(7).CellStyle = styleAtencao;
                                    row.GetCell(40).CellStyle = styleAtencao;
                                }

                                if (lc.DIFCOMPLEMENTO == "1")
                                {
                                    row.GetCell(8).CellStyle = styleAtencao;
                                    row.GetCell(41).CellStyle = styleAtencao;
                                }

                                if (lc.DIFBAIRRO == "1")
                                {
                                    row.GetCell(9).CellStyle = styleAtencao;
                                    row.GetCell(43).CellStyle = styleAtencao;
                                }

                                if (lc.DIFMUNICIPIO == "1")
                                {
                                    row.GetCell(10).CellStyle = styleAtencao;
                                    row.GetCell(44).CellStyle = styleAtencao;
                                }
                                if (lc.DIFUF == "1")
                                {
                                    row.GetCell(11).CellStyle = styleAtencao;
                                    row.GetCell(45).CellStyle = styleAtencao;
                                }

                                if (lc.DIFIBGE == "1")
                                {
                                    row.GetCell(14).CellStyle = styleAtencao;
                                    row.GetCell(28).CellStyle = styleAtencao;
                                }

                                if (lc.DIFCEP == "1")
                                {
                                    row.GetCell(12).CellStyle = styleAtencao;
                                    row.GetCell(42).CellStyle = styleAtencao;
                                }
                                if (lc.DIFIE == "1")
                                {
                                    row.GetCell(13).CellStyle = styleAtencao;
                                    row.GetCell(67).CellStyle = styleAtencao;
                                }
                                if (lc.DIFIE == "2")
                                {
                                    row.GetCell(13).CellStyle = styleCritico;
                                    row.GetCell(67).CellStyle = styleCritico;
                                    row.GetCell(78).CellStyle = styleCritico;
                                }


                            }
                            else
                            {
                                //Multiplos_IE
                                countMie++;
                                var row = sheetMie.CreateRow(countMie);
                                //var row = sheet.CreateRow(count);

                                row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                                row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                                row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                                row.CreateCell(3).SetCellValue(lc.ORCNPJ);
                                row.CreateCell(4).SetCellValue(lc.ORRAZAO);
                                row.CreateCell(5).SetCellValue(lc.ORFANTASIA);
                                row.CreateCell(6).SetCellValue(lc.ORLOGRADOURO);
                                row.CreateCell(7).SetCellValue(lc.ORNUM);
                                row.CreateCell(8).SetCellValue(lc.ORCOMPLEMENTO);
                                row.CreateCell(9).SetCellValue(lc.ORBAIRRO);
                                row.CreateCell(10).SetCellValue(lc.ORMUNICIPIO);
                                row.CreateCell(11).SetCellValue(lc.ORUF);
                                row.CreateCell(12).SetCellValue(lc.ORCEP);
                                row.CreateCell(13).SetCellValue(lc.ORIE);
                                row.CreateCell(14).SetCellValue(lc.ORIBGE);
                                row.CreateCell(15).SetCellValue(lc.ORCCM);
                                row.CreateCell(16).SetCellValue(lc.ORCNAE);
                                row.CreateCell(17).SetCellValue(lc.ORSUFRAMA);
                                row.CreateCell(18).SetCellValue(lc.ORRES0);
                                row.CreateCell(19).SetCellValue(lc.ORRES1);
                                row.CreateCell(20).SetCellValue(lc.ORRES2);
                                row.CreateCell(21).SetCellValue(lc.ORRES3);
                                row.CreateCell(22).SetCellValue(lc.ORRES4);
                                row.CreateCell(23).SetCellValue(lc.ORRES5);
                                row.CreateCell(24).SetCellValue(lc.ORRES6);
                                row.CreateCell(25).SetCellValue(lc.ORRES7);
                                row.CreateCell(26).SetCellValue(lc.ORRES8);
                                row.CreateCell(27).SetCellValue(lc.ORRES9);
                                row.CreateCell(28).SetCellValue(lc.IBCODIBGE);
                                row.CreateCell(29).SetCellValue(lc.RFCNPJ);
                                row.CreateCell(30).SetCellValue(lc.RFDATAABERTURA);
                                row.CreateCell(31).SetCellValue(lc.RFRAZAOSOCIAL);
                                row.CreateCell(32).SetCellValue(lc.RFNOMEFANTASIA);
                                row.CreateCell(33).SetCellValue(lc.RFCODIGOAE);
                                row.CreateCell(34).SetCellValue(lc.RFDESCRICAOAE);
                                row.CreateCell(35).SetCellValue(lc.RFCODIGONJ);
                                row.CreateCell(36).SetCellValue(lc.RFDESCRICAONJ);
                                row.CreateCell(37).SetCellValue(lc.RFCODIGOAESECUNDARIA);
                                row.CreateCell(38).SetCellValue(lc.RFDESCRICAOAESECUNDARIA);
                                row.CreateCell(39).SetCellValue(lc.RFLOGRADOURO);
                                row.CreateCell(40).SetCellValue(lc.RFNUMERO);
                                row.CreateCell(41).SetCellValue(lc.RFCOMPLEMENTO);
                                row.CreateCell(42).SetCellValue(lc.RFCEP);
                                row.CreateCell(43).SetCellValue(lc.RFBAIRRO);
                                row.CreateCell(44).SetCellValue(lc.RFCIDADE);
                                row.CreateCell(45).SetCellValue(lc.RFESTADO);
                                row.CreateCell(46).SetCellValue(lc.RFSITUACAO);
                                row.CreateCell(47).SetCellValue(lc.RFDATASITUACAO);
                                row.CreateCell(48).SetCellValue(lc.RFSITUACAOESPECIAL);
                                row.CreateCell(49).SetCellValue(lc.RFDATASITUACAOESPECIAL);
                                row.CreateCell(50).SetCellValue(lc.RFDATACONSULTA);
                                row.CreateCell(51).SetCellValue(lc.RFHORACONSULTA);
                                row.CreateCell(52).SetCellValue(lc.RFTIPOEMPRESA);
                                row.CreateCell(53).SetCellValue(lc.RFMOTIVOSITUACAO);
                                row.CreateCell(54).SetCellValue(lc.RFERRCOD);
                                row.CreateCell(55).SetCellValue(lc.RFERRDESCRI);
                                row.CreateCell(56).SetCellValue(lc.RFRES0);
                                row.CreateCell(57).SetCellValue(lc.RFRES1);
                                row.CreateCell(58).SetCellValue(lc.RFRES2);
                                row.CreateCell(59).SetCellValue(lc.RFRES3);
                                row.CreateCell(60).SetCellValue(lc.RFRES4);
                                row.CreateCell(61).SetCellValue(lc.RFRES5);
                                row.CreateCell(62).SetCellValue(lc.RFRES6);
                                row.CreateCell(63).SetCellValue(lc.RFRES7);
                                row.CreateCell(64).SetCellValue(lc.RFRES8);
                                row.CreateCell(65).SetCellValue(lc.RFRES9);
                                row.CreateCell(66).SetCellValue(lc.SICNPJ);
                                row.CreateCell(67).SetCellValue(lc.SIIE);
                                row.CreateCell(68).SetCellValue(lc.SIRAZAOSOCIAL);
                                row.CreateCell(69).SetCellValue(lc.SINOMEFANTASIA);
                                row.CreateCell(70).SetCellValue(lc.SILOGRADOURO);
                                row.CreateCell(71).SetCellValue(lc.SINUMERO);
                                row.CreateCell(72).SetCellValue(lc.SICOMPLEMENTO);
                                row.CreateCell(73).SetCellValue(lc.SIBAIRRO);
                                row.CreateCell(74).SetCellValue(lc.SICIDADE);
                                row.CreateCell(75).SetCellValue(lc.SIESTADO);
                                row.CreateCell(76).SetCellValue(lc.SICEP);
                                row.CreateCell(77).SetCellValue(lc.SIEMAIL);
                                row.CreateCell(78).SetCellValue(lc.SISITUACAO);
                                row.CreateCell(79).SetCellValue(lc.SIDATAINCLUSAO);
                                row.CreateCell(80).SetCellValue(lc.SIDATABAIXA);
                                row.CreateCell(81).SetCellValue(lc.SIDATACONSULTA);
                                row.CreateCell(82).SetCellValue(lc.SINUMEROCONSULTA);
                                row.CreateCell(83).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                                row.CreateCell(84).SetCellValue(lc.SIEMISSAONFE);
                                row.CreateCell(85).SetCellValue(lc.SICODINCRA);
                                row.CreateCell(86).SetCellValue(lc.SIREGIMEAPURACAO);
                                row.CreateCell(87).SetCellValue(lc.SIMOTIVOBAIXA);
                                row.CreateCell(88).SetCellValue(lc.SIREGULARIDADEFISCAL);
                                row.CreateCell(89).SetCellValue(lc.SIERRCOD);
                                row.CreateCell(90).SetCellValue(lc.SIERRDESCRI);
                                row.CreateCell(91).SetCellValue(lc.SIRES0);
                                row.CreateCell(92).SetCellValue(lc.SIRES1);
                                row.CreateCell(93).SetCellValue(lc.SIRES2);
                                row.CreateCell(94).SetCellValue(lc.SIRES3);
                                row.CreateCell(95).SetCellValue(lc.SIRES4);
                                row.CreateCell(96).SetCellValue(lc.SIRES5);
                                row.CreateCell(97).SetCellValue(lc.SIRES6);
                                row.CreateCell(98).SetCellValue(lc.SIRES7);
                                row.CreateCell(99).SetCellValue(lc.SIRES8);
                                row.CreateCell(100).SetCellValue(lc.SIRES9);
                                row.CreateCell(101).SetCellValue(lc.SNCNPJ);
                                row.CreateCell(102).SetCellValue(lc.SNRAZAOSOCIAL);
                                row.CreateCell(103).SetCellValue(lc.SNSITUACAO);
                                row.CreateCell(104).SetCellValue(lc.SNDATASITUACAO);
                                row.CreateCell(105).SetCellValue(lc.SNSITUACAOSIMEI);
                                row.CreateCell(106).SetCellValue(lc.SNSITUACAOANTERIOR);
                                row.CreateCell(107).SetCellValue(lc.SNERRCOD);
                                row.CreateCell(108).SetCellValue(lc.SNERRDESCRI);
                                row.CreateCell(109).SetCellValue(lc.SNRES0);
                                row.CreateCell(110).SetCellValue(lc.SNRES1);
                                row.CreateCell(111).SetCellValue(lc.SNRES2);
                                row.CreateCell(112).SetCellValue(lc.SNRES3);
                                row.CreateCell(113).SetCellValue(lc.SNRES4);
                                row.CreateCell(114).SetCellValue(lc.SUCNPJ);
                                row.CreateCell(115).SetCellValue(lc.SUSUFRAMANUM);
                                row.CreateCell(116).SetCellValue(lc.SURAZAOSOCIAL);
                                row.CreateCell(117).SetCellValue(lc.SULOGRADOURO);
                                row.CreateCell(118).SetCellValue(lc.SUNUMERO);
                                row.CreateCell(119).SetCellValue(lc.SUCOMPLEMENTO);
                                row.CreateCell(120).SetCellValue(lc.SUBAIRRO);
                                row.CreateCell(121).SetCellValue(lc.SUESTADO);
                                row.CreateCell(122).SetCellValue(lc.SUCIDADE);
                                row.CreateCell(123).SetCellValue(lc.SUCEP);
                                row.CreateCell(124).SetCellValue(lc.SUTELEFONE);
                                row.CreateCell(125).SetCellValue(lc.SUSITUACAO);
                                row.CreateCell(126).SetCellValue(lc.SUDATACONSULTA);
                                row.CreateCell(127).SetCellValue(lc.SUATIVIDADEECONOMICA);
                                row.CreateCell(128).SetCellValue(lc.SUDATAVALIDADE);
                                row.CreateCell(129).SetCellValue(lc.SUTIPOINCENTIVO);
                                row.CreateCell(130).SetCellValue(lc.SUEMAIL);
                                row.CreateCell(131).SetCellValue(lc.SUPAGINA);
                                row.CreateCell(132).SetCellValue(lc.SUERRCOD);
                                row.CreateCell(133).SetCellValue(lc.SUERRDESCRI);
                                row.CreateCell(134).SetCellValue(lc.SURES0);
                                row.CreateCell(135).SetCellValue(lc.SURES1);
                                row.CreateCell(136).SetCellValue(lc.SURES2);
                                row.CreateCell(137).SetCellValue(lc.SURES3);
                                row.CreateCell(138).SetCellValue(lc.SURES4);
                                row.CreateCell(139).SetCellValue(lc.SURES5);
                                row.CreateCell(140).SetCellValue(lc.SURES6);
                                row.CreateCell(141).SetCellValue(lc.SURES7);
                                row.CreateCell(142).SetCellValue(lc.SURES8);
                                row.CreateCell(143).SetCellValue(lc.SURES9);


                                for (int ii = 0; ii <= 143; ii++)
                                {
                                    row.GetCell(ii).CellStyle = styleNormal;
                                }

                                //if (lc.STATUS == "1")
                                //{ row.GetCell(3).CellStyle = styleAtencao; }
                                //else
                                //{ row.GetCell(3).CellStyle = styleCritico; }

                                // pinta por qtd de comparações
                                try
                                {
                                    int _stts = Convert.ToInt32(lc.STATUS);
                                   // if (_stts >= 2) row.GetCell(0).CellStyle = styleCritico;
                                   // if (_stts == 1) row.GetCell(0).CellStyle = styleAtencao;
                                }
                                catch (Exception ex)
                                {

                                }

                               // if (lc.STATUS == "0") 
                                    row.GetCell(0).CellStyle = styleNormal;

                                if (lc.DIFCNPJ == "2")
                                {
                                    row.GetCell(3).CellStyle = styleCritico;
                                    row.GetCell(29).CellStyle = styleCritico;
                                    row.GetCell(46).CellStyle = styleCritico;
                                }
                                if (lc.DIFCNPJ == "1") row.GetCell(3).CellStyle = styleAtencao;
                                //if (lc.DIFCNPJ == "0") row.GetCell(4).CellStyle = styleNormal;
                                if (lc.DIFRAZAO == "1")
                                {
                                    row.GetCell(4).CellStyle = styleAtencao;
                                    row.GetCell(31).CellStyle = styleAtencao;
                                }
                                if (lc.DIFFANTASIA == "1")
                                {
                                    row.GetCell(5).CellStyle = styleAtencao;
                                    row.GetCell(32).CellStyle = styleAtencao;
                                }

                                if (lc.DIFLOGRADOURO == "1")
                                {
                                    row.GetCell(6).CellStyle = styleAtencao;
                                    row.GetCell(39).CellStyle = styleAtencao;
                                }
                                if (lc.DIFNUM == "1")
                                {
                                    row.GetCell(7).CellStyle = styleAtencao;
                                    row.GetCell(40).CellStyle = styleAtencao;
                                }

                                if (lc.DIFCOMPLEMENTO == "1")
                                {
                                    row.GetCell(8).CellStyle = styleAtencao;
                                    row.GetCell(41).CellStyle = styleAtencao;
                                }

                                if (lc.DIFBAIRRO == "1")
                                {
                                    row.GetCell(9).CellStyle = styleAtencao;
                                    row.GetCell(43).CellStyle = styleAtencao;
                                }

                                if (lc.DIFMUNICIPIO == "1")
                                {
                                    row.GetCell(10).CellStyle = styleAtencao;
                                    row.GetCell(44).CellStyle = styleAtencao;
                                }
                                if (lc.DIFUF == "1")
                                {
                                    row.GetCell(11).CellStyle = styleAtencao;
                                    row.GetCell(45).CellStyle = styleAtencao;
                                }

                                if (lc.DIFIBGE == "1")
                                {
                                    row.GetCell(14).CellStyle = styleAtencao;
                                    row.GetCell(28).CellStyle = styleAtencao;
                                }

                                if (lc.DIFCEP == "1")
                                {
                                    row.GetCell(12).CellStyle = styleAtencao;
                                    row.GetCell(42).CellStyle = styleAtencao;
                                }
                                if (lc.DIFIE == "1")
                                {
                                    row.GetCell(13).CellStyle = styleAtencao;
                                    row.GetCell(67).CellStyle = styleAtencao;
                                }
                                if (lc.DIFIE == "2")
                                {
                                    row.GetCell(13).CellStyle = styleCritico;
                                    row.GetCell(67).CellStyle = styleCritico;
                                    row.GetCell(78).CellStyle = styleCritico;
                                }

                            }
                        }
                        else
                        {
                            var row = sheet.CreateRow(count);

                            row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                            row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                            row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                            row.CreateCell(3).SetCellValue(lc.RFCNPJ);
                            row.CreateCell(4).SetCellValue(lc.RFSITUACAO);
                            row.CreateCell(5).SetCellValue(lc.SIIE);
                            row.CreateCell(6).SetCellValue(lc.SISITUACAO);
                            row.CreateCell(7).SetCellValue(lc.RFRAZAOSOCIAL);
                            row.CreateCell(8).SetCellValue(lc.RFNOMEFANTASIA);
                            row.CreateCell(9).SetCellValue(lc.RFLOGRADOURO);
                            row.CreateCell(10).SetCellValue(lc.RFNUMERO);
                            row.CreateCell(11).SetCellValue(lc.RFCOMPLEMENTO);
                            row.CreateCell(12).SetCellValue(lc.RFBAIRRO);
                            row.CreateCell(13).SetCellValue(lc.RFCIDADE);
                            row.CreateCell(14).SetCellValue(lc.RFESTADO);
                            row.CreateCell(15).SetCellValue(lc.RFCEP.Replace(".", "").Replace("-", ""));
                            row.CreateCell(16).SetCellValue(lc.IBCODIBGE);
                            row.CreateCell(17).SetCellValue(lc.ORCCM);
                            row.CreateCell(18).SetCellValue(lc.RFCODIGOAE.Replace(".", "").Replace("-", ""));
                            row.CreateCell(19).SetCellValue(lc.ORSUFRAMA);
                            for (int ii = 0; ii <= 19; ii++)
                            {
                                row.GetCell(ii).CellStyle = styleNormal;
                            }

                            //try
                            //{
                            //    int _stts = Convert.ToInt32(lc.STATUS);
                            //    if (_stts >= 2) row.GetCell(3).CellStyle = styleCritico;
                            //    if (_stts == 1) row.GetCell(3).CellStyle = styleAtencao;
                            //}
                            //catch (Exception ex)
                            //{ 

                            //}
                            //if (lc.STATUS == "0") row.GetCell(3).CellStyle = styleNormal;

                            if (lc.DIFCNPJ == "2")
                            {
                                row.GetCell(3).CellStyle = styleCritico;
                                row.GetCell(4).CellStyle = styleCritico;
                            }
                            if (lc.DIFCNPJ == "1")
                            {
                                row.GetCell(3).CellStyle = styleAtencao;
                                row.GetCell(4).CellStyle = styleAtencao;
                            }
                            //if (lc.DIFCNPJ == "0") row.GetCell(4).CellStyle = styleNormal;
                            if (lc.DIFIE == "2")
                            {
                                row.GetCell(5).CellStyle = styleCritico;
                                row.GetCell(6).CellStyle = styleCritico;
                            }

                            if (lc.DIFRAZAO == "1")
                            {
                                row.GetCell(7).CellStyle = styleAtencao;
                            }
                            if (lc.DIFFANTASIA == "1")
                            {
                                row.GetCell(8).CellStyle = styleAtencao;
                            }

                            if (lc.DIFLOGRADOURO == "1")
                            {
                                row.GetCell(9).CellStyle = styleAtencao;
                            }
                            if (lc.DIFNUM == "1")
                            {
                                row.GetCell(10).CellStyle = styleAtencao;
                            }

                            if (lc.DIFCOMPLEMENTO == "1")
                            {
                                row.GetCell(11).CellStyle = styleAtencao;
                            }

                            if (lc.DIFBAIRRO == "1")
                            {
                                row.GetCell(12).CellStyle = styleAtencao;
                            }

                            if (lc.DIFMUNICIPIO == "1")
                            {
                                row.GetCell(13).CellStyle = styleAtencao;
                            }
                            if (lc.DIFUF == "1")
                            {
                                row.GetCell(14).CellStyle = styleAtencao;
                            }

                            if (lc.DIFIBGE == "1")
                            {
                                row.GetCell(16).CellStyle = styleAtencao;
                            }

                            if (lc.DIFCEP == "1")
                            {
                                row.GetCell(15).CellStyle = styleAtencao;
                            }
                            if (lc.DIFIE == "1")
                            {
                                row.GetCell(5).CellStyle = styleAtencao;
                            }

                            count++;

                        }
                    }
                    else
                    {
                        count++;
                    }


                }
                log.debug("gravou xls pasta sugestao update");
                //Resumo
                sheet = templateWorkbook.GetSheet("resumo");
                v_resumo vres = db.v_resumos.Single(t => t.PK_IDos == _idos);

                sheet.GetRow(2).GetCell(3).SetCellValue(vres.PK_IDos);
                sheet.GetRow(4).GetCell(2).SetCellValue(vres.RS.ToString());
                sheet.GetRow(6).GetCell(2).SetCellValue(vres.nomecompleto.ToString());
                sheet.GetRow(7).GetCell(2).SetCellValue(((DateTime)vres.dt_reg).ToString("dd/MM/yyyy"));
                //sheet.CreateRow(8).CreateCell(2).SetCellValue("");
                sheet.GetRow(8).GetCell(2).SetCellValue(((int)vres.qtd_registros));

                sheet.GetRow(11).GetCell(2).SetCellValue(((int)vres.qtdprocessados).ToString());
                sheet.GetRow(12).GetCell(2).SetCellValue(((int)vres.qtdCNPJativo).ToString());
                int qtdinativos = (int)vres.qtdCNPJbaixada + (int)vres.qtdCNPJinapta + (int)vres.qtdCNPJinexistente + (int)vres.qtdCNPJinvalido + (int)vres.qtdCNPJnula + (int)vres.qtdCNPJsuspensa;
                sheet.GetRow(13).GetCell(2).SetCellValue(qtdinativos.ToString());
                sheet.GetRow(14).GetCell(2).SetCellValue(((int)vres.qtdCNPJerro).ToString());

                sheet.GetRow(15).GetCell(2).SetCellValue(((int)vres.qtdSIhabilitado).ToString());
                //int ieinativos = (int)vres.qtdSIinabilitado + (int)vres.qtdSIinexistente;
                sheet.GetRow(16).GetCell(2).SetCellValue(((int)vres.qtdSIinabilitado).ToString());
                sheet.GetRow(17).GetCell(2).SetCellValue(((int)vres.qtdSIinexistente).ToString());
                sheet.GetRow(18).GetCell(2).SetCellValue(((int)vres.qtdSIerro).ToString());


                log.debug("gravou xls pasta resumo");

                templateWorkbook.Write(msResult);
                msResult.Close();
                //wb.Worksheets.Add(ws);
                //wb.Save(xlsfile);
                sr.DiscardBufferedData();
                sr.Dispose();
                sr = null;
                templateWorkbook.Dispose();
                templateWorkbook = null;
                msResult.Dispose();
                msResult = null;
                log.debug("export2ExcelPJ concluido");
            }
            catch (Exception ex)
            {
                log.erro("export2excelpj", _idos.ToString(), ex.Message + "|" + ex.Source + "|" + ex.StackTrace + "|" + ex.InnerException + "|" + lc.ORCNPJ);
            }
        }


        public void export2ExcelPF_old()
        {
            try
            {
            string xlsfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls";
            string csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
            cIO cio = new cIO();
            cio.apagaArq(xlsfile);
            cio.copyFile(csrv.TEMPDIR + "/templatePF.xls", xlsfile);

            linhaComplexPF lc = new linhaComplexPF();
            linhaSimplePF ls = new linhaSimplePF();

            FileStream fs = new FileStream(csrv.TEMPDIR + "/templatePF.xls", FileMode.Open, FileAccess.Read);
            FileStream msResult = new FileStream(xlsfile, FileMode.Create);

            HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);


            

            var style1 = templateWorkbook.CreateCellStyle();
            style1.FillBackgroundColor = HSSFColor.WHITE.index;
            style1.FillForegroundColor = HSSFColor.WHITE.index;
            style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
            style1.BorderBottom = CellBorderType.THIN;
            style1.BorderLeft = CellBorderType.THIN;
            style1.BorderRight = CellBorderType.THIN;
            style1.BorderTop = CellBorderType.THIN;
            style1.BottomBorderColor = HSSFColor.BLACK.index;
            style1.LeftBorderColor = HSSFColor.BLACK.index;
            style1.RightBorderColor = HSSFColor.BLACK.index;
            style1.TopBorderColor = HSSFColor.BLACK.index;

            var styleNormal = templateWorkbook.CreateCellStyle();
            styleNormal.FillBackgroundColor = HSSFColor.WHITE.index;
            styleNormal.FillForegroundColor = HSSFColor.WHITE.index;
            styleNormal.FillPattern = FillPatternType.SOLID_FOREGROUND;
            styleNormal.BorderBottom = CellBorderType.THIN;
            styleNormal.BorderLeft = CellBorderType.THIN;
            styleNormal.BorderRight = CellBorderType.THIN;
            styleNormal.BorderTop = CellBorderType.THIN;
            styleNormal.BottomBorderColor = HSSFColor.BLACK.index;
            styleNormal.LeftBorderColor = HSSFColor.BLACK.index;
            styleNormal.RightBorderColor = HSSFColor.BLACK.index;
            styleNormal.TopBorderColor = HSSFColor.BLACK.index;

            var styleAtencao = templateWorkbook.CreateCellStyle();
            styleAtencao.FillBackgroundColor = HSSFColor.YELLOW.index;
            styleAtencao.FillForegroundColor = HSSFColor.YELLOW.index;
            styleAtencao.FillPattern = FillPatternType.SOLID_FOREGROUND;
            styleAtencao.BorderBottom = CellBorderType.THIN;
            styleAtencao.BorderLeft = CellBorderType.THIN;
            styleAtencao.BorderRight = CellBorderType.THIN;
            styleAtencao.BorderTop = CellBorderType.THIN;
            styleAtencao.BottomBorderColor = HSSFColor.BLACK.index;
            styleAtencao.LeftBorderColor = HSSFColor.BLACK.index;
            styleAtencao.RightBorderColor = HSSFColor.BLACK.index;
            styleAtencao.TopBorderColor = HSSFColor.BLACK.index;

            var styleCritico = templateWorkbook.CreateCellStyle();
            styleCritico.FillBackgroundColor = HSSFColor.RED.index;
            styleCritico.FillForegroundColor = HSSFColor.RED.index;
            styleCritico.FillPattern = FillPatternType.SOLID_FOREGROUND;
            styleCritico.BorderBottom = CellBorderType.THIN;
            styleCritico.BorderLeft = CellBorderType.THIN;
            styleCritico.BorderRight = CellBorderType.THIN;
            styleCritico.BorderTop = CellBorderType.THIN;
            styleCritico.BottomBorderColor = HSSFColor.BLACK.index;
            styleCritico.LeftBorderColor = HSSFColor.BLACK.index;
            styleCritico.RightBorderColor = HSSFColor.BLACK.index;
            styleCritico.TopBorderColor = HSSFColor.BLACK.index;


            // consultas
            var sheet = templateWorkbook.GetSheet("Consultas_CPF");
            int count = 0;
            string linha = "";
            Encoding enc = Encoding.GetEncoding("ISO-8859-1");
            StreamReader sr = new StreamReader(csvfile, enc);

            while (sr.Peek() >= 0)
            {
                linha = sr.ReadLine();
                List<string> campos = linha2List(linha);
                ls = new linhaSimplePF();
                ls.autoCarga(campos);
                int cols = 0;

                if (count > 0)
                {
                    // TIPOVERIFICACAO	CODIGO	EMPRESA	FILIAL	CPF	SITUACAOCPF	IE	SITUACAOIE	NOME	LOGRADOURO	NUMERO	COMPLEMENTO	BAIRRO	MUNICIPIO	UF	CEP	IBGE	ERROCOD	ERRODESCRI	RFDATACONSULTA	RFHORACONSULTA
                    var row = sheet.CreateRow(count);

                    row.CreateCell(0).SetCellValue(ls.TIPOVERIFICACAO);
                    row.CreateCell(1).SetCellValue(ls.CODIGO);
                    row.CreateCell(2).SetCellValue(ls.EMPRESA);
                    row.CreateCell(3).SetCellValue(ls.FILIAL);
                    row.CreateCell(4).SetCellValue(ls.CPF);
                    row.CreateCell(5).SetCellValue(ls.SITUACAOCPF);
                    row.CreateCell(6).SetCellValue(ls.IE);
                    row.CreateCell(7).SetCellValue(ls.SITUACAOIE);
                    row.CreateCell(8).SetCellValue(ls.NOME);
                    row.CreateCell(9).SetCellValue(ls.LOGRADOURO);
                    row.CreateCell(10).SetCellValue(ls.NUMERO);
                    row.CreateCell(11).SetCellValue(ls.COMPLEMENTO);
                    row.CreateCell(12).SetCellValue(ls.BAIRRO);
                    row.CreateCell(13).SetCellValue(ls.MUNICIPIO);
                    row.CreateCell(14).SetCellValue(ls.UF);
                    row.CreateCell(15).SetCellValue(ls.CEP);
                    row.CreateCell(16).SetCellValue(ls.IBGE);
                    row.CreateCell(17).SetCellValue(ls.ERROCOD);
                    row.CreateCell(18).SetCellValue(ls.ERRODESCRI);
                    row.CreateCell(19).SetCellValue(ls.RFDATACONSULTA);
                    row.CreateCell(20).SetCellValue(ls.RFHORACONSULTA);

                    for (int ii = 0; ii < 21; ii++)
                    {
                        row.GetCell(ii).CellStyle = styleNormal;
                    }
                    
                    
                    //foreach (string s in campos)
                    //{
                    //    row.CreateCell(cols).SetCellValue(s);
                    //    row.GetCell(cols).CellStyle = styleNormal;
                    //    cols++;
                    //}

                }
                count++;

            }

            //Original
            sheet = templateWorkbook.GetSheet("Origem_CPF");
            csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";

            count = 0;
            linha = "";
            sr = new StreamReader(csvfile, enc);


            while (sr.Peek() >= 0)
            {
                linha = sr.ReadLine();
                List<string> campos = linha2List(linha);
                int cols = 0;
                if (count > 0)
                {
                    var row = sheet.CreateRow(count);
                    foreach (string s in campos)
                    {
                        row.CreateCell(cols).SetCellValue(s);
                        row.GetCell(cols).CellStyle = styleNormal;
                        cols++;
                    }
                }
                count++;

            }


            //Linha complexa
            sheet = templateWorkbook.GetSheet("Saneamento_CPF");
            csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";

            count = 0;
            linha = "";
            sr = new StreamReader(csvfile, enc);


            while (sr.Peek() >= 0)
            {
                linha = sr.ReadLine();
                List<string> campos = linha2List(linha);
                lc = new linhaComplexPF();
                lc.autoCarga(campos);
                int cols = 0;
                if (count > 0)
                {
                    var row = sheet.CreateRow(count);
                    row.CreateCell(0).SetCellValue(lc.CODIGO);
                    row.CreateCell(1).SetCellValue(lc.EMPRESA);
                    row.CreateCell(2).SetCellValue(lc.FILIAL);
                    row.CreateCell(3).SetCellValue(lc.STATUS);
                    row.CreateCell(4).SetCellValue(lc.CPF);
                    row.CreateCell(5).SetCellValue(lc.NOME);
                    row.CreateCell(6).SetCellValue(lc.LOGRADOURO);
                    row.CreateCell(7).SetCellValue(lc.NUM);
                    row.CreateCell(8).SetCellValue(lc.COMPLEMENTO);
                    row.CreateCell(9).SetCellValue(lc.BAIRRO);
                    row.CreateCell(10).SetCellValue(lc.MUNICIPIO);
                    row.CreateCell(11).SetCellValue(lc.UF);
                    row.CreateCell(12).SetCellValue(lc.CEP);
                    row.CreateCell(13).SetCellValue(lc.IBGE);
                    row.CreateCell(14).SetCellValue(lc.RFCPF);
                    row.CreateCell(15).SetCellValue(lc.RFNOME);
                    row.CreateCell(16).SetCellValue(lc.RFSITUACAO);
                    row.CreateCell(17).SetCellValue(lc.RFDATACONSULTA);
                    row.CreateCell(18).SetCellValue(lc.CRLOGRADOURO);
                    row.CreateCell(19).SetCellValue(lc.CRNUMERO);
                    row.CreateCell(20).SetCellValue(lc.CRCOMPLEMENTO);
                    row.CreateCell(21).SetCellValue(lc.CRCEP);
                    row.CreateCell(22).SetCellValue(lc.CRBAIRRO);
                    row.CreateCell(23).SetCellValue(lc.CRCIDADE);
                    row.CreateCell(24).SetCellValue(lc.CRESTADO);
                    row.CreateCell(25).SetCellValue(lc.CRIBGE);
                    row.CreateCell(26).SetCellValue(lc.ERRCOD);
                    row.CreateCell(27).SetCellValue(lc.ERRDESCRI);

                    for (int ii = 0; ii <= 27; ii++)
                    {
                        row.GetCell(ii).CellStyle = styleNormal;
                    }

                    //if (lc.STATUS == "1")
                    //{ row.GetCell(3).CellStyle = styleAtencao; }
                    //else
                    //{ row.GetCell(3).CellStyle = styleCritico; }
                    try
                    {
                        int _stts = Convert.ToInt32(lc.STATUS);
                        if (_stts >= 2) row.GetCell(3).CellStyle = styleCritico;
                        if (_stts == 1) row.GetCell(3).CellStyle = styleAtencao;
                    }
                    catch (Exception ex)
                    {

                    }
                    if (lc.STATUS == "0") row.GetCell(3).CellStyle = styleNormal;

                    if (lc.DIFCPF == "2")
                    {
                        row.GetCell(4).CellStyle = styleCritico;
                        row.GetCell(16).CellStyle = styleCritico;

                    }
                    if (lc.DIFCPF == "1")
                    {
                        row.GetCell(4).CellStyle = styleAtencao;
                    }
                    
                    if (lc.DIFNOME == "1")
                    {
                        row.GetCell(5).CellStyle = styleAtencao;
                        row.GetCell(15).CellStyle = styleAtencao;
                    }
                    if (lc.DIFLOGRADOURO == "1")
                    {
                        row.GetCell(6).CellStyle = styleAtencao;
                        row.GetCell(18).CellStyle = styleAtencao;
                    }
                    
                    if (lc.DIFBAIRRO == "1")
                    {
                        row.GetCell(9).CellStyle = styleAtencao;
                        row.GetCell(22).CellStyle = styleAtencao;
                    }

                    if (lc.DIFMUNICIPIO == "1")
                    {
                        row.GetCell(10).CellStyle = styleAtencao;
                        row.GetCell(23).CellStyle = styleAtencao;
                    }
                    if (lc.DIFUF == "1")
                    {
                        row.GetCell(11).CellStyle = styleAtencao;
                        row.GetCell(24).CellStyle = styleAtencao;
                    }

                    if (lc.DIFIBGE == "1")
                    {
                        row.GetCell(13).CellStyle = styleAtencao;
                        row.GetCell(25).CellStyle = styleAtencao;
                    }

                    if (lc.DIFCEP == "1")
                    {
                        row.GetCell(12).CellStyle = styleAtencao;
                        row.GetCell(21).CellStyle = styleAtencao;
                    }
                    
                }
                count++;

            }

            // Sugestao Update
            // 0 CODIGO	1 EMPRESA	2 FILIAL	3 CPF	4 NOME	5 LOGRADOURO	6 NUM	7 COMPLEMENTO	8 BAIRRO	9 MUNICIPIO	10 UF	11 CEP	12 IBGE


            sheet = templateWorkbook.GetSheet("SUGESTAO_UPDATE_CPF");
            var sheetRes = templateWorkbook.GetSheet("Restricao_CPF");
            csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultasemlinha.txt";

            count = 0;
            int countRes = 0;
            linha = "";
            sr = new StreamReader(csvfile, enc);


            while (sr.Peek() >= 0)
            {
                linha = sr.ReadLine();
                List<string> campos = linha2List(linha);
                lc = new linhaComplexPF();
                lc.autoCarga(campos);
                //int cols = 0;
                if (count > 0)
                {
                    if (Convert.ToInt16(lc.DIFCPF) >= 2)
                    {
                        countRes++;
                        var row = sheetRes.CreateRow(countRes);
                        row.CreateCell(0).SetCellValue(lc.CODIGO);
                        row.CreateCell(1).SetCellValue(lc.EMPRESA);
                        row.CreateCell(2).SetCellValue(lc.FILIAL);
                        row.CreateCell(3).SetCellValue(lc.STATUS);
                        row.CreateCell(4).SetCellValue(lc.CPF);
                        row.CreateCell(5).SetCellValue(lc.NOME);
                        row.CreateCell(6).SetCellValue(lc.LOGRADOURO);
                        row.CreateCell(7).SetCellValue(lc.NUM);
                        row.CreateCell(8).SetCellValue(lc.COMPLEMENTO);
                        row.CreateCell(9).SetCellValue(lc.BAIRRO);
                        row.CreateCell(10).SetCellValue(lc.MUNICIPIO);
                        row.CreateCell(11).SetCellValue(lc.UF);
                        row.CreateCell(12).SetCellValue(lc.CEP);
                        row.CreateCell(13).SetCellValue(lc.IBGE);
                        row.CreateCell(14).SetCellValue(lc.RFCPF);
                        row.CreateCell(15).SetCellValue(lc.RFNOME);
                        row.CreateCell(16).SetCellValue(lc.RFSITUACAO);
                        row.CreateCell(17).SetCellValue(lc.RFDATACONSULTA);
                        row.CreateCell(18).SetCellValue(lc.CRLOGRADOURO);
                        row.CreateCell(19).SetCellValue(lc.CRNUMERO);
                        row.CreateCell(20).SetCellValue(lc.CRCOMPLEMENTO);
                        row.CreateCell(21).SetCellValue(lc.CRCEP);
                        row.CreateCell(22).SetCellValue(lc.CRBAIRRO);
                        row.CreateCell(23).SetCellValue(lc.CRCIDADE);
                        row.CreateCell(24).SetCellValue(lc.CRESTADO);
                        row.CreateCell(25).SetCellValue(lc.CRIBGE);
                        row.CreateCell(26).SetCellValue(lc.ERRCOD);
                        row.CreateCell(27).SetCellValue(lc.ERRDESCRI);

                        for (int ii = 0; ii <= 27; ii++)
                        {
                            row.GetCell(ii).CellStyle = styleNormal;
                        }

                        if (lc.DIFCPF == "2")
                        {
                            row.GetCell(4).CellStyle = styleCritico;
                            row.GetCell(16).CellStyle = styleCritico;

                        }

                        if (lc.DIFCPF == "1")
                        {
                            row.GetCell(4).CellStyle = styleAtencao;
                        }

                        if (lc.DIFNOME == "1")
                        {
                            row.GetCell(5).CellStyle = styleAtencao;
                            row.GetCell(15).CellStyle = styleAtencao;
                        }
                        if (lc.DIFLOGRADOURO == "1")
                        {
                            row.GetCell(6).CellStyle = styleAtencao;
                            row.GetCell(18).CellStyle = styleAtencao;
                        }

                        if (lc.DIFBAIRRO == "1")
                        {
                            row.GetCell(9).CellStyle = styleAtencao;
                            row.GetCell(22).CellStyle = styleAtencao;
                        }

                        if (lc.DIFMUNICIPIO == "1")
                        {
                            row.GetCell(10).CellStyle = styleAtencao;
                            row.GetCell(23).CellStyle = styleAtencao;
                        }
                        if (lc.DIFUF == "1")
                        {
                            row.GetCell(11).CellStyle = styleAtencao;
                            row.GetCell(24).CellStyle = styleAtencao;
                        }

                        if (lc.DIFIBGE == "1")
                        {
                            row.GetCell(13).CellStyle = styleAtencao;
                            row.GetCell(25).CellStyle = styleAtencao;
                        }

                        if (lc.DIFCEP == "1")
                        {
                            row.GetCell(12).CellStyle = styleAtencao;
                            row.GetCell(21).CellStyle = styleAtencao;
                        }

                    }
                    else
                    {
                        var row = sheet.CreateRow(count);

                        row.CreateCell(0).SetCellValue(lc.CODIGO);
                        row.CreateCell(1).SetCellValue(lc.EMPRESA);
                        row.CreateCell(2).SetCellValue(lc.FILIAL);
                        row.CreateCell(3).SetCellValue(lc.RFCPF);
                        row.CreateCell(4).SetCellValue(lc.RFNOME);
                        row.CreateCell(5).SetCellValue(lc.CRLOGRADOURO);
                        row.CreateCell(6).SetCellValue(lc.NUM);
                        row.CreateCell(7).SetCellValue(lc.COMPLEMENTO);
                        row.CreateCell(8).SetCellValue(lc.CRBAIRRO);
                        row.CreateCell(9).SetCellValue(lc.CRCIDADE);
                        row.CreateCell(10).SetCellValue(lc.CRESTADO);
                        row.CreateCell(11).SetCellValue(lc.CRCEP);
                        row.CreateCell(12).SetCellValue(lc.CRIBGE);

                        for (int ii = 0; ii <= 12; ii++)
                        {
                            row.GetCell(ii).CellStyle = styleNormal;
                        }

                        if (lc.DIFCPF == "2")
                        {
                            row.GetCell(3).CellStyle = styleCritico;
                            row.GetCell(4).CellStyle = styleCritico;
                        }
                        if (lc.DIFCPF == "1")
                        {
                            row.GetCell(3).CellStyle = styleAtencao;
                            row.GetCell(4).CellStyle = styleAtencao;
                        }

                        
                        
                        if (lc.DIFNOME == "1")
                        {
                            row.GetCell(4).CellStyle = styleAtencao;
                        }
                        if (lc.DIFLOGRADOURO == "1")
                        {
                            row.GetCell(5).CellStyle = styleAtencao;
                        }

                        if (lc.DIFBAIRRO == "1")
                        {
                            row.GetCell(8).CellStyle = styleAtencao;
                        }

                        if (lc.DIFMUNICIPIO == "1")
                        {
                            row.GetCell(9).CellStyle = styleAtencao;
                        }
                        if (lc.DIFUF == "1")
                        {
                            row.GetCell(10).CellStyle = styleAtencao;
                        }

                        if (lc.DIFIBGE == "1")
                        {
                            row.GetCell(12).CellStyle = styleAtencao;
                        }

                        if (lc.DIFCEP == "1")
                        {
                            row.GetCell(11).CellStyle = styleAtencao;
                        }


                        count++;
                    }
                }
                else
                {
                    count++;
                }
                

            }

            //Resumo
            sheet = templateWorkbook.GetSheet("resumo");
            //sheet.CreateRow(8).CreateCell(3).SetCellValue("8 - ainda em teste");
            v_resumo vres = db.v_resumos.Single(t => t.PK_IDos == _idos);

            sheet.GetRow(2).GetCell(3).SetCellValue(vres.PK_IDos);
            sheet.GetRow(4).GetCell(2).SetCellValue(vres.RS.ToString());
            sheet.GetRow(6).GetCell(2).SetCellValue(vres.nomecompleto.ToString());
            sheet.GetRow(7).GetCell(2).SetCellValue(((DateTime)vres.dt_reg).ToString("dd/MM/yyyy"));
            sheet.GetRow(8).GetCell(2).SetCellValue(((int)vres.qtd_registros));

            sheet.GetRow(11).GetCell(2).SetCellValue(((int)vres.qtdprocessados));
            sheet.GetRow(12).GetCell(2).SetCellValue(((int)vres.qtdCPFativo));
            int qtdinativos = (int)vres.qtdCPFinativo;
            sheet.GetRow(13).GetCell(2).SetCellValue(qtdinativos);
            sheet.GetRow(14).GetCell(2).SetCellValue(((int)vres.qtdCPFerro));

            sheet.GetRow(15).GetCell(2).SetCellValue(((int)vres.qtdCEPs));
            //int ieinativos = (int)vres.qtdSIinabilitado + (int)vres.qtdSIinexistente;
            sheet.GetRow(16).GetCell(2).SetCellValue(((int)vres.qtdCEPinvalido));
            sheet.GetRow(17).GetCell(2).SetCellValue(((int)vres.qtdCEPerro));
            //sheet.GetRow(18).GetCell(2).SetCellValue(((int)vres.qtdSIerro));

            templateWorkbook.Write(msResult);
            msResult.Close();
            //wb.Worksheets.Add(ws);
            //wb.Save(xlsfile);
            sr.DiscardBufferedData();
            sr.Dispose();
            sr = null;

            }
            catch (Exception ex)
            {
                log.erro("export2excelpf", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        public void export2ExcelPF()
        {
            try
            {
                string xlsfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls";
                string csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";
                cIO cio = new cIO();
                cio.apagaArq(xlsfile);
                cio.copyFile(csrv.TEMPDIR + "/templatePF.xls", xlsfile);

                linhaComplexPFV1 lc = new linhaComplexPFV1();
               // linhaSimplePF ls = new linhaSimplePF();

                FileStream fs = new FileStream(csrv.TEMPDIR + "/templatePF.xls", FileMode.Open, FileAccess.Read);
                FileStream msResult = new FileStream(xlsfile, FileMode.Create);

                HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true);




                var style1 = templateWorkbook.CreateCellStyle();
                style1.FillBackgroundColor = HSSFColor.WHITE.index;
                style1.FillForegroundColor = HSSFColor.WHITE.index;
                style1.FillPattern = FillPatternType.SOLID_FOREGROUND;
                style1.BorderBottom = CellBorderType.THIN;
                style1.BorderLeft = CellBorderType.THIN;
                style1.BorderRight = CellBorderType.THIN;
                style1.BorderTop = CellBorderType.THIN;
                style1.BottomBorderColor = HSSFColor.BLACK.index;
                style1.LeftBorderColor = HSSFColor.BLACK.index;
                style1.RightBorderColor = HSSFColor.BLACK.index;
                style1.TopBorderColor = HSSFColor.BLACK.index;

                var styleNormal = templateWorkbook.CreateCellStyle();
                styleNormal.FillBackgroundColor = HSSFColor.WHITE.index;
                styleNormal.FillForegroundColor = HSSFColor.WHITE.index;
                styleNormal.FillPattern = FillPatternType.SOLID_FOREGROUND;
                styleNormal.BorderBottom = CellBorderType.THIN;
                styleNormal.BorderLeft = CellBorderType.THIN;
                styleNormal.BorderRight = CellBorderType.THIN;
                styleNormal.BorderTop = CellBorderType.THIN;
                styleNormal.BottomBorderColor = HSSFColor.BLACK.index;
                styleNormal.LeftBorderColor = HSSFColor.BLACK.index;
                styleNormal.RightBorderColor = HSSFColor.BLACK.index;
                styleNormal.TopBorderColor = HSSFColor.BLACK.index;

                var styleAtencao = templateWorkbook.CreateCellStyle();
                styleAtencao.FillBackgroundColor = HSSFColor.YELLOW.index;
                styleAtencao.FillForegroundColor = HSSFColor.YELLOW.index;
                styleAtencao.FillPattern = FillPatternType.SOLID_FOREGROUND;
                styleAtencao.BorderBottom = CellBorderType.THIN;
                styleAtencao.BorderLeft = CellBorderType.THIN;
                styleAtencao.BorderRight = CellBorderType.THIN;
                styleAtencao.BorderTop = CellBorderType.THIN;
                styleAtencao.BottomBorderColor = HSSFColor.BLACK.index;
                styleAtencao.LeftBorderColor = HSSFColor.BLACK.index;
                styleAtencao.RightBorderColor = HSSFColor.BLACK.index;
                styleAtencao.TopBorderColor = HSSFColor.BLACK.index;

                var styleCritico = templateWorkbook.CreateCellStyle();
                styleCritico.FillBackgroundColor = HSSFColor.RED.index;
                styleCritico.FillForegroundColor = HSSFColor.RED.index;
                styleCritico.FillPattern = FillPatternType.SOLID_FOREGROUND;
                styleCritico.BorderBottom = CellBorderType.THIN;
                styleCritico.BorderLeft = CellBorderType.THIN;
                styleCritico.BorderRight = CellBorderType.THIN;
                styleCritico.BorderTop = CellBorderType.THIN;
                styleCritico.BottomBorderColor = HSSFColor.BLACK.index;
                styleCritico.LeftBorderColor = HSSFColor.BLACK.index;
                styleCritico.RightBorderColor = HSSFColor.BLACK.index;
                styleCritico.TopBorderColor = HSSFColor.BLACK.index;


                //// consultas
                //var sheet = templateWorkbook.GetSheet("Consultas_CPF");
                int count = 0;
                string linha = "";
                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csvfile, enc);

                //while (sr.Peek() >= 0)
                //{
                //    linha = sr.ReadLine();
                //    List<string> campos = linha2List(linha);
                //    ls = new linhaSimplePF();
                //    ls.autoCarga(campos);
                //    int cols = 0;

                //    if (count > 0)
                //    {
                //        // TIPOVERIFICACAO	CODIGO	EMPRESA	FILIAL	CPF	SITUACAOCPF	IE	SITUACAOIE	NOME	LOGRADOURO	NUMERO	COMPLEMENTO	BAIRRO	MUNICIPIO	UF	CEP	IBGE	ERROCOD	ERRODESCRI	RFDATACONSULTA	RFHORACONSULTA
                //        var row = sheet.CreateRow(count);

                //        row.CreateCell(0).SetCellValue(ls.TIPOVERIFICACAO);
                //        row.CreateCell(1).SetCellValue(ls.CODIGO);
                //        row.CreateCell(2).SetCellValue(ls.EMPRESA);
                //        row.CreateCell(3).SetCellValue(ls.FILIAL);
                //        row.CreateCell(4).SetCellValue(ls.CPF);
                //        row.CreateCell(5).SetCellValue(ls.SITUACAOCPF);
                //        row.CreateCell(6).SetCellValue(ls.IE);
                //        row.CreateCell(7).SetCellValue(ls.SITUACAOIE);
                //        row.CreateCell(8).SetCellValue(ls.NOME);
                //        row.CreateCell(9).SetCellValue(ls.LOGRADOURO);
                //        row.CreateCell(10).SetCellValue(ls.NUMERO);
                //        row.CreateCell(11).SetCellValue(ls.COMPLEMENTO);
                //        row.CreateCell(12).SetCellValue(ls.BAIRRO);
                //        row.CreateCell(13).SetCellValue(ls.MUNICIPIO);
                //        row.CreateCell(14).SetCellValue(ls.UF);
                //        row.CreateCell(15).SetCellValue(ls.CEP);
                //        row.CreateCell(16).SetCellValue(ls.IBGE);
                //        row.CreateCell(17).SetCellValue(ls.ERROCOD);
                //        row.CreateCell(18).SetCellValue(ls.ERRODESCRI);
                //        row.CreateCell(19).SetCellValue(ls.RFDATACONSULTA);
                //        row.CreateCell(20).SetCellValue(ls.RFHORACONSULTA);

                //        for (int ii = 0; ii < 21; ii++)
                //        {
                //            row.GetCell(ii).CellStyle = styleNormal;
                //        }


                //        //foreach (string s in campos)
                //        //{
                //        //    row.CreateCell(cols).SetCellValue(s);
                //        //    row.GetCell(cols).CellStyle = styleNormal;
                //        //    cols++;
                //        //}

                //    }
                //    count++;

                //}

                //Original
                var sheet = templateWorkbook.GetSheet("Origem_CPF");
                csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/baseoriginal.txt";

                count = 0;
                linha = "";
                sr = new StreamReader(csvfile, enc);


                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                   
                    //List<string> campos = linha2List(linha);
                    int cols = 0;
                    if (count > 0)
                    {
                        lc = new linhaComplexPFV1();
                        lc.autoCarga(linha);

                        var row = sheet.CreateRow(count);
                        row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                        row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                        row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                        row.CreateCell(3).SetCellValue(lc.ORCPF);
                        row.CreateCell(4).SetCellValue(lc.ORNOME);
                        row.CreateCell(5).SetCellValue(lc.ORLOGRADOURO);
                        row.CreateCell(6).SetCellValue(lc.ORNUM);
                        row.CreateCell(7).SetCellValue(lc.ORCOMPLEMENTO);
                        row.CreateCell(8).SetCellValue(lc.ORBAIRRO);
                        row.CreateCell(9).SetCellValue(lc.ORMUNICIPIO);
                        row.CreateCell(10).SetCellValue(lc.ORUF);
                        row.CreateCell(11).SetCellValue(lc.ORCEP);
                        row.CreateCell(12).SetCellValue(lc.ORIBGE);
                        
                        for (int i = 0; i < 13; i++)
                        {
                            row.GetCell(i).CellStyle = styleNormal;
                        }
                    }
                    count++;

                }
                log.debug("gerada planilha original CPF");

                //Linha complexa
                sheet = templateWorkbook.GetSheet("Saneamento_CPF");
                csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";

                count = 0;
                linha = "";
                sr = new StreamReader(csvfile, enc);


                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    //List<string> campos = linha2List(linha);
                    lc = new linhaComplexPFV1();
                    lc.autoCarga(linha);
                    int cols = 0;
                    if (count > 0)
                    {
                        var row = sheet.CreateRow(count);
                        row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                        row.CreateCell(1 ).SetCellValue(lc.OREMPRESA);
                        row.CreateCell(2 ).SetCellValue(lc.ORFILIAL);
                        row.CreateCell(3 ).SetCellValue(lc.ORCPF);
                        row.CreateCell(4 ).SetCellValue(lc.ORNOME);
                        row.CreateCell(5 ).SetCellValue(lc.ORLOGRADOURO);
                        row.CreateCell(6 ).SetCellValue(lc.ORNUM);
                        row.CreateCell(7 ).SetCellValue(lc.ORCOMPLEMENTO);
                        row.CreateCell(8 ).SetCellValue(lc.ORBAIRRO);
                        row.CreateCell(9 ).SetCellValue(lc.ORMUNICIPIO);
                        row.CreateCell(10).SetCellValue(lc.ORUF);
                        row.CreateCell(11).SetCellValue(lc.ORCEP);
                        row.CreateCell(12).SetCellValue(lc.ORIE);
                        row.CreateCell(13).SetCellValue(lc.ORIBGE);
                        row.CreateCell(14).SetCellValue(lc.RFCPF);
                        row.CreateCell(15).SetCellValue(lc.RFNOME);
                        row.CreateCell(16).SetCellValue(lc.RFCODIGOCONSULTA);
                        row.CreateCell(17).SetCellValue(lc.RFDATACONSULTA);
                        row.CreateCell(18).SetCellValue(lc.RFSITUACAO);
                        row.CreateCell(19).SetCellValue(lc.RFERRCOD);
                        row.CreateCell(20).SetCellValue(lc.RFERRDESCRI);
                        row.CreateCell(21).SetCellValue(lc.RFRES01);
                        row.CreateCell(22).SetCellValue(lc.RFRES02);
                        row.CreateCell(23).SetCellValue(lc.RFRES03);
                        row.CreateCell(24).SetCellValue(lc.RFRES04);
                        row.CreateCell(25).SetCellValue(lc.RFRES05);
                        row.CreateCell(26).SetCellValue(lc.COLOGRADOURO);
                        row.CreateCell(27).SetCellValue(lc.CONUMERO);
                        row.CreateCell(28).SetCellValue(lc.COCOMPLEMENTO);
                        row.CreateCell(29).SetCellValue(lc.COCEP);
                        row.CreateCell(30).SetCellValue(lc.COBAIRRO);
                        row.CreateCell(31).SetCellValue(lc.COCIDADE);
                        row.CreateCell(32).SetCellValue(lc.COESTADO);
                        row.CreateCell(33).SetCellValue(lc.COERRCOD);
                        row.CreateCell(34).SetCellValue(lc.COERRDESCRI);
                        row.CreateCell(35).SetCellValue(lc.CORES01);
                        row.CreateCell(36).SetCellValue(lc.CORES02);
                        row.CreateCell(37).SetCellValue(lc.CORES03);
                        row.CreateCell(38).SetCellValue(lc.CORES04);
                        row.CreateCell(39).SetCellValue(lc.IBCODIBGE);
                        row.CreateCell(40).SetCellValue(lc.SICPF);
                        row.CreateCell(41).SetCellValue(lc.SIIE);
                        row.CreateCell(42).SetCellValue(lc.SIRAZAOSOCIAL);
                        row.CreateCell(43).SetCellValue(lc.SINOMEFANTASIA);
                        row.CreateCell(44).SetCellValue(lc.SILOGRADOURO);
                        row.CreateCell(45).SetCellValue(lc.SINUMERO);
                        row.CreateCell(46).SetCellValue(lc.SICOMPLEMENTO);
                        row.CreateCell(47).SetCellValue(lc.SIBAIRRO);
                        row.CreateCell(48).SetCellValue(lc.SICIDADE);
                        row.CreateCell(49).SetCellValue(lc.SIESTADO);
                        row.CreateCell(50).SetCellValue(lc.SICEP);
                        row.CreateCell(51).SetCellValue(lc.SIEMAIL);
                        row.CreateCell(52).SetCellValue(lc.SISITUACAO);
                        row.CreateCell(53).SetCellValue(lc.SIDATAINCLUSAO);
                        row.CreateCell(54).SetCellValue(lc.SIDATABAIXA);
                        row.CreateCell(55).SetCellValue(lc.SIDATACONSULTA);
                        row.CreateCell(56).SetCellValue(lc.SINUMEROCONSULTA);
                        row.CreateCell(57).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                        row.CreateCell(58).SetCellValue(lc.SIEMISSAONFE);
                        row.CreateCell(59).SetCellValue(lc.SICODINCRA);
                        row.CreateCell(60).SetCellValue(lc.SIREGIMEAPURACAO);
                        row.CreateCell(61).SetCellValue(lc.SIMOTIVOBAIXA);
                        row.CreateCell(62).SetCellValue(lc.SIREGULARIDADEFISCAL);
                        row.CreateCell(63).SetCellValue(lc.SIERRCOD);
                        row.CreateCell(64).SetCellValue(lc.SIERRDESCRI);
                        row.CreateCell(65).SetCellValue(lc.SIRES0);
                        row.CreateCell(66).SetCellValue(lc.SIRES1);
                        row.CreateCell(67).SetCellValue(lc.SIRES2);
                        row.CreateCell(68).SetCellValue(lc.SIRES3);
                        row.CreateCell(69).SetCellValue(lc.SIRES4);
                        row.CreateCell(70).SetCellValue(lc.SIRES5);
                        row.CreateCell(71).SetCellValue(lc.SIRES6);
                        row.CreateCell(72).SetCellValue(lc.SIRES7);
                        row.CreateCell(73).SetCellValue(lc.SIRES8);
                        row.CreateCell(74).SetCellValue(lc.SIRES9);
                        row.CreateCell(75).SetCellValue(lc.ERRCOD);
                        row.CreateCell(76).SetCellValue(lc.ERRDESCRI);

                        for (int ii = 0; ii <= 76; ii++)
                        {
                            row.GetCell(ii).CellStyle = styleNormal;
                        }

                        //if (lc.STATUS == "1")
                        //{ row.GetCell(3).CellStyle = styleAtencao; }
                        //else
                        //{ row.GetCell(3).CellStyle = styleCritico; }
                        //try
                        //{
                        //    int _stts = Convert.ToInt32(lc.STATUS);
                        //    if (_stts >= 2) row.GetCell(3).CellStyle = styleCritico;
                        //    if (_stts == 1) row.GetCell(3).CellStyle = styleAtencao;
                        //}
                        //catch (Exception ex)
                        //{

                        //}
                        if (lc.STATUS == "0") row.GetCell(3).CellStyle = styleNormal;

                        if (lc.DIFCPF == "2")
                        {
                            row.GetCell(3).CellStyle = styleCritico;
                            row.GetCell(14).CellStyle = styleCritico;

                        }
                        if (lc.DIFCPF == "1")
                        {
                            row.GetCell(3).CellStyle = styleAtencao;
                        }

                        if (lc.DIFNOME == "1")
                        {
                            row.GetCell(4).CellStyle = styleAtencao;
                            row.GetCell(15).CellStyle = styleAtencao;
                        }
                        if (lc.DIFLOGRADOURO == "1")
                        {
                            row.GetCell(5).CellStyle = styleAtencao;
                            row.GetCell(26).CellStyle = styleAtencao;
                        }

                        if (lc.DIFBAIRRO == "1")
                        {
                            row.GetCell(8).CellStyle = styleAtencao;
                            row.GetCell(30).CellStyle = styleAtencao;
                        }

                        if (lc.DIFMUNICIPIO == "1")
                        {
                            row.GetCell(9).CellStyle = styleAtencao;
                            row.GetCell(31).CellStyle = styleAtencao;
                        }
                        if (lc.DIFUF == "1")
                        {
                            row.GetCell(10).CellStyle = styleAtencao;
                            row.GetCell(32).CellStyle = styleAtencao;
                        }

                        if (lc.DIFIBGE == "1")
                        {
                            row.GetCell(13).CellStyle = styleAtencao;
                            row.GetCell(39).CellStyle = styleAtencao;
                        }

                        if (lc.DIFCEP == "1")
                        {
                            row.GetCell(11).CellStyle = styleAtencao;
                            row.GetCell(29).CellStyle = styleAtencao;
                        }
                        if (lc.DIFCEP == "2")
                        {
                            row.GetCell(11).CellStyle = styleCritico;
                            row.GetCell(29).CellStyle = styleCritico;
                        }
                    }
                    count++;

                }
                log.debug("gerada planilha SANEAMENTO_CPF");

                // Sugestao Update
                // 0 CODIGO	1 EMPRESA	2 FILIAL	3 CPF	4 NOME	5 LOGRADOURO	6 NUM	7 COMPLEMENTO	8 BAIRRO	9 MUNICIPIO	10 UF	11 CEP	12 IBGE


                sheet = templateWorkbook.GetSheet("SUGESTAO_UPDATE_CPF");
                var sheetRes = templateWorkbook.GetSheet("Restricao_CPF");
                var sheetCEP = templateWorkbook.GetSheet("Restricao_CEP");
                csvfile = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";

                count = 0;
                int countRes = 0;
                int countCep = 0;
                linha = "";
                sr = new StreamReader(csvfile, enc);


                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    List<string> campos = linha2List(linha);
                    lc = new linhaComplexPFV1();
                    lc.autoCarga(campos);
                    //int cols = 0;
                    if (count > 0)
                    {
                        if (Convert.ToInt16(lc.DIFCPF) >= 2)
                        {
                            countRes++;
                            var row = sheetRes.CreateRow(countRes);
                            row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                            row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                            row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                            row.CreateCell(3).SetCellValue(lc.ORCPF);
                            row.CreateCell(4).SetCellValue(lc.ORNOME);
                            row.CreateCell(5).SetCellValue(lc.ORLOGRADOURO);
                            row.CreateCell(6).SetCellValue(lc.ORNUM);
                            row.CreateCell(7).SetCellValue(lc.ORCOMPLEMENTO);
                            row.CreateCell(8).SetCellValue(lc.ORBAIRRO);
                            row.CreateCell(9).SetCellValue(lc.ORMUNICIPIO);
                            row.CreateCell(10).SetCellValue(lc.ORUF);
                            row.CreateCell(11).SetCellValue(lc.ORCEP);
                            row.CreateCell(12).SetCellValue(lc.ORIE);
                            row.CreateCell(13).SetCellValue(lc.ORIBGE);
                            row.CreateCell(14).SetCellValue(lc.RFCPF);
                            row.CreateCell(15).SetCellValue(lc.RFNOME);
                            row.CreateCell(16).SetCellValue(lc.RFCODIGOCONSULTA);
                            row.CreateCell(17).SetCellValue(lc.RFDATACONSULTA);
                            row.CreateCell(18).SetCellValue(lc.RFSITUACAO);
                            row.CreateCell(19).SetCellValue(lc.RFERRCOD);
                            row.CreateCell(20).SetCellValue(lc.RFERRDESCRI);
                            row.CreateCell(21).SetCellValue(lc.RFRES01);
                            row.CreateCell(22).SetCellValue(lc.RFRES02);
                            row.CreateCell(23).SetCellValue(lc.RFRES03);
                            row.CreateCell(24).SetCellValue(lc.RFRES04);
                            row.CreateCell(25).SetCellValue(lc.RFRES05);
                            row.CreateCell(26).SetCellValue(lc.COLOGRADOURO);
                            row.CreateCell(27).SetCellValue(lc.CONUMERO);
                            row.CreateCell(28).SetCellValue(lc.COCOMPLEMENTO);
                            row.CreateCell(29).SetCellValue(lc.COCEP);
                            row.CreateCell(30).SetCellValue(lc.COBAIRRO);
                            row.CreateCell(31).SetCellValue(lc.COCIDADE);
                            row.CreateCell(32).SetCellValue(lc.COESTADO);
                            row.CreateCell(33).SetCellValue(lc.COERRCOD);
                            row.CreateCell(34).SetCellValue(lc.COERRDESCRI);
                            row.CreateCell(35).SetCellValue(lc.CORES01);
                            row.CreateCell(36).SetCellValue(lc.CORES02);
                            row.CreateCell(37).SetCellValue(lc.CORES03);
                            row.CreateCell(38).SetCellValue(lc.CORES04);
                            row.CreateCell(39).SetCellValue(lc.IBCODIBGE);
                            row.CreateCell(40).SetCellValue(lc.SICPF);
                            row.CreateCell(41).SetCellValue(lc.SIIE);
                            row.CreateCell(42).SetCellValue(lc.SIRAZAOSOCIAL);
                            row.CreateCell(43).SetCellValue(lc.SINOMEFANTASIA);
                            row.CreateCell(44).SetCellValue(lc.SILOGRADOURO);
                            row.CreateCell(45).SetCellValue(lc.SINUMERO);
                            row.CreateCell(46).SetCellValue(lc.SICOMPLEMENTO);
                            row.CreateCell(47).SetCellValue(lc.SIBAIRRO);
                            row.CreateCell(48).SetCellValue(lc.SICIDADE);
                            row.CreateCell(49).SetCellValue(lc.SIESTADO);
                            row.CreateCell(50).SetCellValue(lc.SICEP);
                            row.CreateCell(51).SetCellValue(lc.SIEMAIL);
                            row.CreateCell(52).SetCellValue(lc.SISITUACAO);
                            row.CreateCell(53).SetCellValue(lc.SIDATAINCLUSAO);
                            row.CreateCell(54).SetCellValue(lc.SIDATABAIXA);
                            row.CreateCell(55).SetCellValue(lc.SIDATACONSULTA);
                            row.CreateCell(56).SetCellValue(lc.SINUMEROCONSULTA);
                            row.CreateCell(57).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                            row.CreateCell(58).SetCellValue(lc.SIEMISSAONFE);
                            row.CreateCell(59).SetCellValue(lc.SICODINCRA);
                            row.CreateCell(60).SetCellValue(lc.SIREGIMEAPURACAO);
                            row.CreateCell(61).SetCellValue(lc.SIMOTIVOBAIXA);
                            row.CreateCell(62).SetCellValue(lc.SIREGULARIDADEFISCAL);
                            row.CreateCell(63).SetCellValue(lc.SIERRCOD);
                            row.CreateCell(64).SetCellValue(lc.SIERRDESCRI);
                            row.CreateCell(65).SetCellValue(lc.SIRES0);
                            row.CreateCell(66).SetCellValue(lc.SIRES1);
                            row.CreateCell(67).SetCellValue(lc.SIRES2);
                            row.CreateCell(68).SetCellValue(lc.SIRES3);
                            row.CreateCell(69).SetCellValue(lc.SIRES4);
                            row.CreateCell(70).SetCellValue(lc.SIRES5);
                            row.CreateCell(71).SetCellValue(lc.SIRES6);
                            row.CreateCell(72).SetCellValue(lc.SIRES7);
                            row.CreateCell(73).SetCellValue(lc.SIRES8);
                            row.CreateCell(74).SetCellValue(lc.SIRES9);
                            row.CreateCell(75).SetCellValue(lc.ERRCOD);
                            row.CreateCell(76).SetCellValue(lc.ERRDESCRI);

                            for (int ii = 0; ii <= 76; ii++)
                            {
                                row.GetCell(ii).CellStyle = styleNormal;
                            }

                            //if (lc.STATUS == "1")
                            //{ row.GetCell(3).CellStyle = styleAtencao; }
                            //else
                            //{ row.GetCell(3).CellStyle = styleCritico; }
                            //try
                            //{
                            //    int _stts = Convert.ToInt32(lc.STATUS);
                            //    //if (_stts >= 2) row.GetCell(3).CellStyle = styleCritico;
                            //    //if (_stts == 1) row.GetCell(3).CellStyle = styleAtencao;
                            //}
                            //catch (Exception ex)
                            //{

                            //}
                            if (lc.STATUS == "0") row.GetCell(3).CellStyle = styleNormal;

                            if (lc.DIFCPF == "2")
                            {
                                row.GetCell(3).CellStyle = styleCritico;
                                row.GetCell(14).CellStyle = styleCritico;

                            }
                            if (lc.DIFCPF == "1")
                            {
                                row.GetCell(3).CellStyle = styleAtencao;
                            }

                            if (lc.DIFNOME == "1")
                            {
                                row.GetCell(4).CellStyle = styleAtencao;
                                row.GetCell(15).CellStyle = styleAtencao;
                            }
                            if (lc.DIFLOGRADOURO == "1")
                            {
                                row.GetCell(5).CellStyle = styleAtencao;
                                row.GetCell(26).CellStyle = styleAtencao;
                            }

                            if (lc.DIFBAIRRO == "1")
                            {
                                row.GetCell(8).CellStyle = styleAtencao;
                                row.GetCell(30).CellStyle = styleAtencao;
                            }

                            if (lc.DIFMUNICIPIO == "1")
                            {
                                row.GetCell(9).CellStyle = styleAtencao;
                                row.GetCell(31).CellStyle = styleAtencao;
                            }
                            if (lc.DIFUF == "1")
                            {
                                row.GetCell(10).CellStyle = styleAtencao;
                                row.GetCell(32).CellStyle = styleAtencao;
                            }

                            if (lc.DIFIBGE == "1")
                            {
                                row.GetCell(13).CellStyle = styleAtencao;
                                row.GetCell(39).CellStyle = styleAtencao;
                            }

                            if (lc.DIFCEP == "1")
                            {
                                row.GetCell(11).CellStyle = styleAtencao;
                                row.GetCell(29).CellStyle = styleAtencao;
                            }
                            if (lc.DIFCEP == "2")
                            {
                                row.GetCell(11).CellStyle = styleCritico;
                                row.GetCell(29).CellStyle = styleCritico;
                            }
                        }
                        else
                        {
                            if (Convert.ToInt16(lc.DIFCEP) >= 2)
                            {
                                countCep++;
                                var row = sheetCEP.CreateRow(countCep);
                                row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                                row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                                row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                                row.CreateCell(3).SetCellValue(lc.ORCPF);
                                row.CreateCell(4).SetCellValue(lc.ORNOME);
                                row.CreateCell(5).SetCellValue(lc.ORLOGRADOURO);
                                row.CreateCell(6).SetCellValue(lc.ORNUM);
                                row.CreateCell(7).SetCellValue(lc.ORCOMPLEMENTO);
                                row.CreateCell(8).SetCellValue(lc.ORBAIRRO);
                                row.CreateCell(9).SetCellValue(lc.ORMUNICIPIO);
                                row.CreateCell(10).SetCellValue(lc.ORUF);
                                row.CreateCell(11).SetCellValue(lc.ORCEP);
                                row.CreateCell(12).SetCellValue(lc.ORIE);
                                row.CreateCell(13).SetCellValue(lc.ORIBGE);
                                row.CreateCell(14).SetCellValue(lc.RFCPF);
                                row.CreateCell(15).SetCellValue(lc.RFNOME);
                                row.CreateCell(16).SetCellValue(lc.RFCODIGOCONSULTA);
                                row.CreateCell(17).SetCellValue(lc.RFDATACONSULTA);
                                row.CreateCell(18).SetCellValue(lc.RFSITUACAO);
                                row.CreateCell(19).SetCellValue(lc.RFERRCOD);
                                row.CreateCell(20).SetCellValue(lc.RFERRDESCRI);
                                row.CreateCell(21).SetCellValue(lc.RFRES01);
                                row.CreateCell(22).SetCellValue(lc.RFRES02);
                                row.CreateCell(23).SetCellValue(lc.RFRES03);
                                row.CreateCell(24).SetCellValue(lc.RFRES04);
                                row.CreateCell(25).SetCellValue(lc.RFRES05);
                                row.CreateCell(26).SetCellValue(lc.COLOGRADOURO);
                                row.CreateCell(27).SetCellValue(lc.CONUMERO);
                                row.CreateCell(28).SetCellValue(lc.COCOMPLEMENTO);
                                row.CreateCell(29).SetCellValue(lc.COCEP);
                                row.CreateCell(30).SetCellValue(lc.COBAIRRO);
                                row.CreateCell(31).SetCellValue(lc.COCIDADE);
                                row.CreateCell(32).SetCellValue(lc.COESTADO);
                                row.CreateCell(33).SetCellValue(lc.COERRCOD);
                                row.CreateCell(34).SetCellValue(lc.COERRDESCRI);
                                row.CreateCell(35).SetCellValue(lc.CORES01);
                                row.CreateCell(36).SetCellValue(lc.CORES02);
                                row.CreateCell(37).SetCellValue(lc.CORES03);
                                row.CreateCell(38).SetCellValue(lc.CORES04);
                                row.CreateCell(39).SetCellValue(lc.IBCODIBGE);
                                row.CreateCell(40).SetCellValue(lc.SICPF);
                                row.CreateCell(41).SetCellValue(lc.SIIE);
                                row.CreateCell(42).SetCellValue(lc.SIRAZAOSOCIAL);
                                row.CreateCell(43).SetCellValue(lc.SINOMEFANTASIA);
                                row.CreateCell(44).SetCellValue(lc.SILOGRADOURO);
                                row.CreateCell(45).SetCellValue(lc.SINUMERO);
                                row.CreateCell(46).SetCellValue(lc.SICOMPLEMENTO);
                                row.CreateCell(47).SetCellValue(lc.SIBAIRRO);
                                row.CreateCell(48).SetCellValue(lc.SICIDADE);
                                row.CreateCell(49).SetCellValue(lc.SIESTADO);
                                row.CreateCell(50).SetCellValue(lc.SICEP);
                                row.CreateCell(51).SetCellValue(lc.SIEMAIL);
                                row.CreateCell(52).SetCellValue(lc.SISITUACAO);
                                row.CreateCell(53).SetCellValue(lc.SIDATAINCLUSAO);
                                row.CreateCell(54).SetCellValue(lc.SIDATABAIXA);
                                row.CreateCell(55).SetCellValue(lc.SIDATACONSULTA);
                                row.CreateCell(56).SetCellValue(lc.SINUMEROCONSULTA);
                                row.CreateCell(57).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                                row.CreateCell(58).SetCellValue(lc.SIEMISSAONFE);
                                row.CreateCell(59).SetCellValue(lc.SICODINCRA);
                                row.CreateCell(60).SetCellValue(lc.SIREGIMEAPURACAO);
                                row.CreateCell(61).SetCellValue(lc.SIMOTIVOBAIXA);
                                row.CreateCell(62).SetCellValue(lc.SIREGULARIDADEFISCAL);
                                row.CreateCell(63).SetCellValue(lc.SIERRCOD);
                                row.CreateCell(64).SetCellValue(lc.SIERRDESCRI);
                                row.CreateCell(65).SetCellValue(lc.SIRES0);
                                row.CreateCell(66).SetCellValue(lc.SIRES1);
                                row.CreateCell(67).SetCellValue(lc.SIRES2);
                                row.CreateCell(68).SetCellValue(lc.SIRES3);
                                row.CreateCell(69).SetCellValue(lc.SIRES4);
                                row.CreateCell(70).SetCellValue(lc.SIRES5);
                                row.CreateCell(71).SetCellValue(lc.SIRES6);
                                row.CreateCell(72).SetCellValue(lc.SIRES7);
                                row.CreateCell(73).SetCellValue(lc.SIRES8);
                                row.CreateCell(74).SetCellValue(lc.SIRES9);
                                row.CreateCell(75).SetCellValue(lc.ERRCOD);
                                row.CreateCell(76).SetCellValue(lc.ERRDESCRI);

                                for (int ii = 0; ii <= 76; ii++)
                                {
                                    row.GetCell(ii).CellStyle = styleNormal;
                                }

                                //if (lc.STATUS == "1")
                                //{ row.GetCell(3).CellStyle = styleAtencao; }
                                //else
                                //{ row.GetCell(3).CellStyle = styleCritico; }
                                //try
                                //{
                                //    int _stts = Convert.ToInt32(lc.STATUS);
                                //    //if (_stts >= 2) row.GetCell(3).CellStyle = styleCritico;
                                //    //if (_stts == 1) row.GetCell(3).CellStyle = styleAtencao;
                                //}
                                //catch (Exception ex)
                                //{

                                //}
                                if (lc.STATUS == "0") row.GetCell(3).CellStyle = styleNormal;

                                if (lc.DIFCPF == "2")
                                {
                                    row.GetCell(3).CellStyle = styleCritico;
                                    row.GetCell(14).CellStyle = styleCritico;

                                }
                                if (lc.DIFCPF == "1")
                                {
                                    row.GetCell(3).CellStyle = styleAtencao;
                                }

                                if (lc.DIFNOME == "1")
                                {
                                    row.GetCell(4).CellStyle = styleAtencao;
                                    row.GetCell(15).CellStyle = styleAtencao;
                                }
                                if (lc.DIFLOGRADOURO == "1")
                                {
                                    row.GetCell(5).CellStyle = styleAtencao;
                                    row.GetCell(26).CellStyle = styleAtencao;
                                }

                                if (lc.DIFBAIRRO == "1")
                                {
                                    row.GetCell(8).CellStyle = styleAtencao;
                                    row.GetCell(30).CellStyle = styleAtencao;
                                }

                                if (lc.DIFMUNICIPIO == "1")
                                {
                                    row.GetCell(9).CellStyle = styleAtencao;
                                    row.GetCell(31).CellStyle = styleAtencao;
                                }
                                if (lc.DIFUF == "1")
                                {
                                    row.GetCell(10).CellStyle = styleAtencao;
                                    row.GetCell(32).CellStyle = styleAtencao;
                                }

                                if (lc.DIFIBGE == "1")
                                {
                                    row.GetCell(13).CellStyle = styleAtencao;
                                    row.GetCell(39).CellStyle = styleAtencao;
                                }

                                if (lc.DIFCEP == "1")
                                {
                                    row.GetCell(11).CellStyle = styleAtencao;
                                    row.GetCell(29).CellStyle = styleAtencao;
                                }
                                if (lc.DIFCEP == "2")
                                {
                                    row.GetCell(11).CellStyle = styleCritico;
                                    row.GetCell(29).CellStyle = styleCritico;
                                }
                            }
                            else
                            {
                                var row = sheet.CreateRow(count);

                                row.CreateCell(0).SetCellValue(lc.ORCODIGO);
                                row.CreateCell(1).SetCellValue(lc.OREMPRESA);
                                row.CreateCell(2).SetCellValue(lc.ORFILIAL);
                                row.CreateCell(3).SetCellValue(lc.ORCPF);
                                row.CreateCell(4).SetCellValue(lc.ORNOME);
                                row.CreateCell(5).SetCellValue(lc.ORLOGRADOURO);
                                row.CreateCell(6).SetCellValue(lc.ORNUM);
                                row.CreateCell(7).SetCellValue(lc.ORCOMPLEMENTO);
                                row.CreateCell(8).SetCellValue(lc.ORBAIRRO);
                                row.CreateCell(9).SetCellValue(lc.ORMUNICIPIO);
                                row.CreateCell(10).SetCellValue(lc.ORUF);
                                row.CreateCell(11).SetCellValue(lc.ORCEP);
                                row.CreateCell(12).SetCellValue(lc.ORIE);
                                row.CreateCell(13).SetCellValue(lc.ORIBGE);
                                row.CreateCell(14).SetCellValue(lc.RFCPF);
                                row.CreateCell(15).SetCellValue(lc.RFNOME);
                                row.CreateCell(16).SetCellValue(lc.RFCODIGOCONSULTA);
                                row.CreateCell(17).SetCellValue(lc.RFDATACONSULTA);
                                row.CreateCell(18).SetCellValue(lc.RFSITUACAO);
                                row.CreateCell(19).SetCellValue(lc.RFERRCOD);
                                row.CreateCell(20).SetCellValue(lc.RFERRDESCRI);
                                row.CreateCell(21).SetCellValue(lc.RFRES01);
                                row.CreateCell(22).SetCellValue(lc.RFRES02);
                                row.CreateCell(23).SetCellValue(lc.RFRES03);
                                row.CreateCell(24).SetCellValue(lc.RFRES04);
                                row.CreateCell(25).SetCellValue(lc.RFRES05);
                                row.CreateCell(26).SetCellValue(lc.COLOGRADOURO);
                                row.CreateCell(27).SetCellValue(lc.CONUMERO);
                                row.CreateCell(28).SetCellValue(lc.COCOMPLEMENTO);
                                row.CreateCell(29).SetCellValue(lc.COCEP);
                                row.CreateCell(30).SetCellValue(lc.COBAIRRO);
                                row.CreateCell(31).SetCellValue(lc.COCIDADE);
                                row.CreateCell(32).SetCellValue(lc.COESTADO);
                                row.CreateCell(33).SetCellValue(lc.COERRCOD);
                                row.CreateCell(34).SetCellValue(lc.COERRDESCRI);
                                row.CreateCell(35).SetCellValue(lc.CORES01);
                                row.CreateCell(36).SetCellValue(lc.CORES02);
                                row.CreateCell(37).SetCellValue(lc.CORES03);
                                row.CreateCell(38).SetCellValue(lc.CORES04);
                                row.CreateCell(39).SetCellValue(lc.IBCODIBGE);
                                row.CreateCell(40).SetCellValue(lc.SICPF);
                                row.CreateCell(41).SetCellValue(lc.SIIE);
                                row.CreateCell(42).SetCellValue(lc.SIRAZAOSOCIAL);
                                row.CreateCell(43).SetCellValue(lc.SINOMEFANTASIA);
                                row.CreateCell(44).SetCellValue(lc.SILOGRADOURO);
                                row.CreateCell(45).SetCellValue(lc.SINUMERO);
                                row.CreateCell(46).SetCellValue(lc.SICOMPLEMENTO);
                                row.CreateCell(47).SetCellValue(lc.SIBAIRRO);
                                row.CreateCell(48).SetCellValue(lc.SICIDADE);
                                row.CreateCell(49).SetCellValue(lc.SIESTADO);
                                row.CreateCell(50).SetCellValue(lc.SICEP);
                                row.CreateCell(51).SetCellValue(lc.SIEMAIL);
                                row.CreateCell(52).SetCellValue(lc.SISITUACAO);
                                row.CreateCell(53).SetCellValue(lc.SIDATAINCLUSAO);
                                row.CreateCell(54).SetCellValue(lc.SIDATABAIXA);
                                row.CreateCell(55).SetCellValue(lc.SIDATACONSULTA);
                                row.CreateCell(56).SetCellValue(lc.SINUMEROCONSULTA);
                                row.CreateCell(57).SetCellValue(lc.SIENQUADRAMENTOEMPRESA);
                                row.CreateCell(58).SetCellValue(lc.SIEMISSAONFE);
                                row.CreateCell(59).SetCellValue(lc.SICODINCRA);
                                row.CreateCell(60).SetCellValue(lc.SIREGIMEAPURACAO);
                                row.CreateCell(61).SetCellValue(lc.SIMOTIVOBAIXA);
                                row.CreateCell(62).SetCellValue(lc.SIREGULARIDADEFISCAL);
                                row.CreateCell(63).SetCellValue(lc.SIERRCOD);
                                row.CreateCell(64).SetCellValue(lc.SIERRDESCRI);
                                row.CreateCell(65).SetCellValue(lc.SIRES0);
                                row.CreateCell(66).SetCellValue(lc.SIRES1);
                                row.CreateCell(67).SetCellValue(lc.SIRES2);
                                row.CreateCell(68).SetCellValue(lc.SIRES3);
                                row.CreateCell(69).SetCellValue(lc.SIRES4);
                                row.CreateCell(70).SetCellValue(lc.SIRES5);
                                row.CreateCell(71).SetCellValue(lc.SIRES6);
                                row.CreateCell(72).SetCellValue(lc.SIRES7);
                                row.CreateCell(73).SetCellValue(lc.SIRES8);
                                row.CreateCell(74).SetCellValue(lc.SIRES9);
                                row.CreateCell(75).SetCellValue(lc.ERRCOD);
                                row.CreateCell(76).SetCellValue(lc.ERRDESCRI);

                                for (int ii = 0; ii <= 76; ii++)
                                {
                                    row.GetCell(ii).CellStyle = styleNormal;
                                }

                                //if (lc.STATUS == "1")
                                //{ row.GetCell(3).CellStyle = styleAtencao; }
                                //else
                                //{ row.GetCell(3).CellStyle = styleCritico; }
                                //try
                                //{
                                //    int _stts = Convert.ToInt32(lc.STATUS);
                                //    if (_stts >= 2) row.GetCell(3).CellStyle = styleCritico;
                                //    if (_stts == 1) row.GetCell(3).CellStyle = styleAtencao;
                                //}
                                //catch (Exception ex)
                                //{

                                //}
                                if (lc.STATUS == "0") row.GetCell(3).CellStyle = styleNormal;

                                if (lc.DIFCPF == "2")
                                {
                                    row.GetCell(3).CellStyle = styleCritico;
                                    row.GetCell(14).CellStyle = styleCritico;

                                }
                                if (lc.DIFCPF == "1")
                                {
                                    row.GetCell(3).CellStyle = styleAtencao;
                                }

                                if (lc.DIFNOME == "1")
                                {
                                    row.GetCell(4).CellStyle = styleAtencao;
                                    row.GetCell(15).CellStyle = styleAtencao;
                                }
                                if (lc.DIFLOGRADOURO == "1")
                                {
                                    row.GetCell(5).CellStyle = styleAtencao;
                                    row.GetCell(26).CellStyle = styleAtencao;
                                }

                                if (lc.DIFBAIRRO == "1")
                                {
                                    row.GetCell(8).CellStyle = styleAtencao;
                                    row.GetCell(30).CellStyle = styleAtencao;
                                }

                                if (lc.DIFMUNICIPIO == "1")
                                {
                                    row.GetCell(9).CellStyle = styleAtencao;
                                    row.GetCell(31).CellStyle = styleAtencao;
                                }
                                if (lc.DIFUF == "1")
                                {
                                    row.GetCell(10).CellStyle = styleAtencao;
                                    row.GetCell(32).CellStyle = styleAtencao;
                                }

                                if (lc.DIFIBGE == "1")
                                {
                                    row.GetCell(13).CellStyle = styleAtencao;
                                    row.GetCell(39).CellStyle = styleAtencao;
                                }

                                if (lc.DIFCEP == "1")
                                {
                                    row.GetCell(11).CellStyle = styleAtencao;
                                    row.GetCell(29).CellStyle = styleAtencao;
                                }
                                if (lc.DIFCEP == "2")
                                {
                                    row.GetCell(11).CellStyle = styleCritico;
                                    row.GetCell(29).CellStyle = styleCritico;
                                }
                                 count++;
                            }

                            
                        }
                       
                    }
                    else
                    {
                        count++;
                    }


                }
                log.debug("gerada planilha SUGESTAO_UPDATE_CPF e restricao");

                //Resumo
                sheet = templateWorkbook.GetSheet("resumo");
                //sheet.CreateRow(8).CreateCell(3).SetCellValue("8 - ainda em teste");
                v_resumo vres = db.v_resumos.Single(t => t.PK_IDos == _idos);

                sheet.GetRow(2).GetCell(3).SetCellValue(vres.PK_IDos);
                sheet.GetRow(4).GetCell(2).SetCellValue(vres.RS.ToString());
                sheet.GetRow(6).GetCell(2).SetCellValue(vres.nomecompleto.ToString());
                sheet.GetRow(7).GetCell(2).SetCellValue(((DateTime)vres.dt_reg).ToString("dd/MM/yyyy"));
                sheet.GetRow(8).GetCell(2).SetCellValue(((int)vres.qtd_registros));

                sheet.GetRow(11).GetCell(2).SetCellValue(((int)vres.qtdprocessados));
                sheet.GetRow(12).GetCell(2).SetCellValue(((int)vres.qtdCPFativo));
                int qtdinativos = (int)vres.qtdCPFinativo;
                sheet.GetRow(13).GetCell(2).SetCellValue(qtdinativos);
                sheet.GetRow(14).GetCell(2).SetCellValue(((int)vres.qtdCPFerro));

                sheet.GetRow(15).GetCell(2).SetCellValue(((int)vres.qtdCEPs));
                //int ieinativos = (int)vres.qtdSIinabilitado + (int)vres.qtdSIinexistente;
                sheet.GetRow(16).GetCell(2).SetCellValue(((int)vres.qtdCEPinvalido));
                sheet.GetRow(17).GetCell(2).SetCellValue(((int)vres.qtdCEPerro));
                //sheet.GetRow(18).GetCell(2).SetCellValue(((int)vres.qtdSIerro));

                templateWorkbook.Write(msResult);
                msResult.Close();
                //wb.Worksheets.Add(ws);
                //wb.Save(xlsfile);
                sr.DiscardBufferedData();
                sr.Dispose();
                sr = null;

            }
            catch (Exception ex)
            {
                log.erro("export2excelpf", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }



        /// <summary>
        /// Higieniza, compara e faz estatistica.
        /// </summary>
        public void higienizaECompara()
        {
            try
            {
                if (!db.tb_os_resumos.Any(t => t.FK_IDos == _idos))
                {
                    tb_os_resumo tbr = new tb_os_resumo();
                    tbr.FK_IDos = _idos;
                    db.tb_os_resumos.InsertOnSubmit(tbr);
                    db.SubmitChanges();

                }

                tb_os_resumo osEstat = db.tb_os_resumos.Single(t => t.FK_IDos == _idos);
                osEstat.qtdCNPJativo = 0;
                osEstat.qtdCNPJbaixada = 0;
                osEstat.qtdCNPJerro = 0;
                osEstat.qtdCNPJinapta = 0;
                osEstat.qtdCNPJinexistente = 0;
                osEstat.qtdCNPJinvalido = 0;
                osEstat.qtdCNPJnula = 0;
                osEstat.qtdCNPJs = 0;
                osEstat.qtdCNPJsuspensa = 0;
                osEstat.qtdprocessados = 0;
                osEstat.qtdregistros = 0;
                osEstat.qtdSIerro = 0;
                osEstat.qtdSIhabilitado = 0;
                osEstat.qtdSIinabilitado = 0;
                osEstat.qtdSIinexistente = 0;
                osEstat.qtdSImultiplos = 0;
                osEstat.qtdSIs = 0;
                osEstat.qtdCEPerro = 0;
                osEstat.qtdCEPinvalido = 0;
                osEstat.qtdCEPs = 0;
                osEstat.qtdCPFativo = 0;
                osEstat.qtdCPFerro = 0;
                osEstat.qtdCPFinativo = 0;
                db.SubmitChanges();


                string linha = "";
                linhaComplex lc = new linhaComplex();
                cIO cio = new cIO();
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
                cio.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
                cio = null;

                string arquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";

                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt", enc);

                int count = 0;

                while (sr.Peek() >= 0)
                {
                    linha = sr.ReadLine();
                    if (count != 0)
                    {
                        lc.autoCarga(linha);
                        lc.normatiza();
                        lc.comparar();
                        osEstat.qtdregistros++;
                        if (lc.RF == "ERR") osEstat.qtdCNPJerro++;
                        if (lc.SI == "ERR") osEstat.qtdSIerro++;
                        if (lc.RF == "OK") osEstat.qtdCNPJs++;
                        if (lc.RF == "OK") osEstat.qtdprocessados++;
                        if (lc.RFSITUACAO == "ATIVA") osEstat.qtdCNPJativo++;
                        if (lc.RFSITUACAO == "SUSPENSA") osEstat.qtdCNPJsuspensa++;
                        if (lc.RFSITUACAO == "INAPTA") osEstat.qtdCNPJinapta++;
                        if (lc.RFSITUACAO == "BAIXADA") osEstat.qtdCNPJbaixada++;
                        if (lc.RFSITUACAO == "NULA") osEstat.qtdCNPJnula++;
                        //if (lc.RFSITUACAO == "ERRO") osEstat.qtdCNPJerro++;
                        string[] sisit = lc.SISITUACAO.Split(';');
                        if (lc.SI == "OK")
                        {
                            foreach (string s in sisit)
                            {

                                switch (s.Trim())
                                {
                                    case "ATIVA":
                                    case "ATIVO":
                                    case "HABILITADO":
                                    case "HABILITADA":
                                        osEstat.qtdSIhabilitado++;
                                        break;
                                    case "ISENTO":
                                    case "NAO CADASTRADO":
                                        osEstat.qtdSIinexistente++;
                                        break;
                                    case "":

                                        //break;
                                    case "ERRO":
                                        osEstat.qtdSIerro++;
                                        break;
                                    default:
                                        osEstat.qtdSIinabilitado++;
                                        break;

                                }
                                osEstat.qtdSIs++;
                            }

                        }
                        else
                        {
                            osEstat.qtdSIerro++;
                        }



                        FW.cIO.appendLine(arquivo, lc.LINHA);
                    }
                    else
                    {
                        FW.cIO.appendLine(arquivo, lc.CABECALHO);
                    }
                    count++;
                }

                db.SubmitChanges();

                log.debug("higienizou arquivo");
            }
            catch (Exception ex)
            {
                log.erro("higienizaeCompara", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }

        }

        public void higienizaEComparaPF()
        {
            try
            {
                if (!db.tb_os_resumos.Any(t => t.FK_IDos == _idos))
                {
                    tb_os_resumo tbr = new tb_os_resumo();
                    tbr.FK_IDos = _idos;
                    db.tb_os_resumos.InsertOnSubmit(tbr);
                    db.SubmitChanges();

                }

                tb_os_resumo osEstat = db.tb_os_resumos.Single(t => t.FK_IDos == _idos);
                osEstat.qtdCNPJativo = 0;
                osEstat.qtdCNPJbaixada = 0;
                osEstat.qtdCNPJerro = 0;
                osEstat.qtdCNPJinapta = 0;
                osEstat.qtdCNPJinexistente = 0;
                osEstat.qtdCNPJinvalido = 0;
                osEstat.qtdCNPJnula = 0;
                osEstat.qtdCNPJs = 0;
                osEstat.qtdCNPJsuspensa = 0;
                osEstat.qtdprocessados = 0;
                osEstat.qtdregistros = 0;
                osEstat.qtdSIerro = 0;
                osEstat.qtdSIhabilitado = 0;
                osEstat.qtdSIinabilitado = 0;
                osEstat.qtdSIinexistente = 0;
                osEstat.qtdSImultiplos = 0;
                osEstat.qtdSIs = 0;
                osEstat.qtdCEPerro = 0;
                osEstat.qtdCEPinvalido = 0;
                osEstat.qtdCEPs = 0;
                osEstat.qtdCPFativo = 0;
                osEstat.qtdCPFerro = 0;
                osEstat.qtdCPFinativo = 0;
                db.SubmitChanges();


                string linha = "";
                linhaComplexPFV1 lc = new linhaComplexPFV1();
                cIO cio = new cIO();
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
                cio.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt");
                cio.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt");
                cio = null;

                string arquivo = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/consultas.txt";

                Encoding enc = Encoding.GetEncoding("ISO-8859-1");
                StreamReader sr = new StreamReader(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/tempconsultas.txt", enc);

                int count = 0;

                while (sr.Peek() >= 0)
                {
                    lc = new linhaComplexPFV1();
                    linha = sr.ReadLine();
                    if (count != 0)
                    {
                        lc.autoCarga(linha);
                        lc.normatiza();
                        lc.comparar();
                        osEstat.qtdregistros++;
                        if (lc.RF == "ERR") osEstat.qtdCPFerro++;
                        if (lc.SI == "ERR") osEstat.qtdSIerro++;
                        if (lc.RF == "OK") osEstat.qtdCNPJs++;
                        if (lc.RF == "OK") osEstat.qtdprocessados++;
                        if (lc.RFSITUACAO == "REGULAR") osEstat.qtdCPFativo++;
                        //if (lc.RFSITUACAO != "SUSPENSA") osEstat.qtdCNPJsuspensa++;
                        if (lc.RFSITUACAO != "REGULAR") osEstat.qtdCPFinativo++;
                        //if (lc.RFSITUACAO == "BAIXADA") osEstat.qtdCNPJbaixada++;
                        //if (lc.RFSITUACAO == "NULA") osEstat.qtdCNPJnula++;
                        //if (lc.RFSITUACAO == "ERRO") osEstat.qtdCNPJerro++;
                        
                        if (lc.ORCEP != "")
                        {

                            if (lc.COERRCOD != "")
                            {
                                osEstat.qtdCEPinvalido++;
                            }
                            else
                            {
                               
                                osEstat.qtdCEPs++;
                            }

                        }
                        else
                        {
                            osEstat.qtdCEPerro++;
                        }



                        FW.cIO.appendLine(arquivo, lc.LINHA);
                    }
                    else
                    {
                        FW.cIO.appendLine(arquivo, lc.CABECALHO);
                    }
                    count++;
                }

                db.SubmitChanges();

                log.debug("higienizou arquivo");
            }
            catch (Exception ex)
            {
                log.erro("higienizaeCompara", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }

        }


        public void zipArquivosPJ()
        {
            try
            {
                log.debug("inicio Zip Arquivos");
                FW.zip z = new FW.zip();
                string nomearqcompzip = string.Format("comprovantes_kcw_PJ_os{0}_{1}-{2}-{3}.zip", _idos.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());
                string nomearqretornozip = string.Format("retorno_kcw_PJ_os{0}_{1}-{2}-{3}.zip", _idos.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());
                string nomearqretorno = string.Format("retorno_kcw_PJ_os{0}_{1}-{2}-{3}.xls", _idos.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());

                z.zipDir(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqcompzip, csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/comprovantes");
                //z.addFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + "comprov.zip", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + "original.txt.2.3");
                z = null;

                cIO io = new cIO();
                // renomeai xls
                io.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretorno);
                // apaga xls antigo
                io.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls");
                io = null;

                // zipa XLS
                z = new FW.zip();
                z.zipArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretornozip, csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretorno, "retorno");

                // atualiza dados da OS, finalização do servico
                tb_o upos = db.tb_os.Single(t => t.PK_IDos == _idos);
                upos.caminhoFinal = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretornozip;
                upos.caminhoComprovantes = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqcompzip;
                //upos.stts = 10;
                upos.status = "Serviço Executado";
                upos.terminoOK = true;
                upos.dt_termino = DateTime.Now;
                db.SubmitChanges();
                upos = null;
                log.debug("Fim zip arquivos");
             }
            catch (Exception ex)
            {
                log.erro("ziparquivos", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        public void zipArquivosPF()
        {
            try
            {
                log.debug("inicio Zip Arquivos");
                FW.zip z = new FW.zip();
                string nomearqcompzip = string.Format("comprovantes_kcw_PF_os{0}_{1}-{2}-{3}.zip", _idos.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());
                string nomearqretornozip = string.Format("retorno_kcw_PF_os{0}_{1}-{2}-{3}.zip", _idos.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());
                string nomearqretorno = string.Format("retorno_kcw_PF_os{0}_{1}-{2}-{3}.xls", _idos.ToString(), DateTime.Now.Day.ToString(), DateTime.Now.Month.ToString(), DateTime.Now.Year.ToString());

                z.zipDir(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqcompzip, csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/comprovantes");
                //z.addFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + "comprov.zip", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + "original.txt.2.3");
                z = null;

                cIO io = new cIO();
                // renomeai xls
                io.copyFile(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls", csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretorno);
                // apaga xls antigo
                io.apagaArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/resultado.xls");
                io = null;

                // zipa XLS
                z = new FW.zip();
                z.zipArq(csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretornozip, csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretorno, "retorno");

                // atualiza dados da OS, finalização do servico
                tb_o upos = db.tb_os.Single(t => t.PK_IDos == _idos);
                upos.caminhoFinal = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqretornozip;
                upos.caminhoComprovantes = csrv.TEMPDIR + "/" + _idemp.ToString() + "/" + _idos.ToString() + "/" + nomearqcompzip;
                //upos.stts = 10;
                upos.status = "Serviço Executado";
                upos.terminoOK = true;
                upos.dt_termino = DateTime.Now;
                db.SubmitChanges();
                upos = null;
                log.debug("Fim zip arquivos");
            }
            catch (Exception ex)
            {
                log.erro("ziparquivos", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        public void finalizaPJ()
        {
            try
            {
            acaoThread = "RUN";
            // stts = 4 em processamento
            tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
            o.status = "Fechando Arquivos";
            o.stts = 8;
            db.SubmitChanges();
            o = null;
            string tt = acaoThread;

            higienizaECompara();

            Reg2Nreg();
            //nRegistrosPara1RegistroPJ();
            
            export2ExcelPJ();
            
            zipArquivosPJ();

            o = db.tb_os.Single(t => t.PK_IDos == _idos);
            db.Refresh(System.Data.Linq.RefreshMode.KeepChanges, o);
            //o.stts = (erros) ? 5 : 9;
            if (acaoThread == "RUN")
            {
                    o.status = "OS finalizada";
                    o.stts = 10;
                    o.dt_proxExec = DateTime.Now.AddHours(1d);
                    acaoThread = "FIM";
            }
            else
            {
                o.status = "Thread Finaliza Cancelada";
                o.stts = 7;
                o.dt_proxExec = DateTime.Now;
                acaoThread = "CNL";
            }
            db.SubmitChanges();
            o = null;
            enviaEmailAviso();
            }
            catch (Exception ex)
            {
                log.erro("finalizapj", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        public void finalizaPF()
        {
            try
            {
            acaoThread = "RUN";
            tb_o o = db.tb_os.Single(t => t.PK_IDos == _idos);
            o.status = "Fechando Arquivos";
            o.stts = 8;
            db.SubmitChanges();
            o = null;

            higienizaEComparaPF();
            
            Reg2NregPF();

            
            export2ExcelPF();
            
            zipArquivosPF();

            o = db.tb_os.Single(t => t.PK_IDos == _idos);
            db.Refresh(System.Data.Linq.RefreshMode.KeepChanges, o);
            //o.stts = (erros) ? 5 : 9;
            if (acaoThread == "RUN")
            {
                o.status = "OS finalizada";
                o.stts = 10;
                o.dt_proxExec = DateTime.Now.AddHours(1d);
                acaoThread = "FIM";
            }
            else
            {
                o.status = "Thread Finaliza Cancelada";
                o.stts = 7;
                o.dt_proxExec = DateTime.Now;
                acaoThread = "CNL";
            }
            db.SubmitChanges();
            o = null;

            enviaEmailAviso();
            }
            catch (Exception ex)
            {
                log.erro("finalizapf", _idos.ToString(), ex.Message + "|" + ex.StackTrace);
            }
        }

        private void enviaEmailAviso()
        {
            FW.crypt cr = new crypt();
            //System.Diagnostics.Debugger.Launch();
             string caminho = Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);

             FW.montaTexto mt = new montaTexto(caminho + "\\emailfim.txt");
             mt.addItem("<!IDOS!>", _idos.ToString());
             mt.trocaTodos();

            tb_empresa tbe = db.tb_empresas.Single(t => t.PK_IDemp == _idemp);
            FW.mandaEmail me = new mandaEmail(csrv.SMTP, csrv.SMTPUSUARIO, cr.encrypt(crchave , csrv.SMTPSENHA));
            me.FROM = csrv.SMTPUSUARIO;
            me.TITULO = "KeyConsultas-web OS finalizada";
            me.TO = tbe.emailcontato;
            me.BODY = mt.textotratado;
            me.enviaMsg();
            me = null;
            mt = null;
            log.debug("Email de aviso fim OS Enviado");
        }
    }

    public class tconsulta
    {
        private long _idos;
        private int _idemp;
        private int _idthread;
        private int _tpps;
        private Task task;
        kservDataContext db;
        

        private fwlog log;
        private configSRV csrv;
        //private Thread th;

        public tconsulta(long IDOS, int IDEMP, int IDTHREAD, int TPPS)
        {
            _idos = IDOS;
            _idemp = IDEMP;
            _idthread = IDTHREAD;
            _tpps = TPPS;
            csrv = new configSRV();
            log = new fwlog(csrv.TEMPDIR + "\\log.dbiKcad.txt", csrv.TIPOLOG);
            db = new kservDataContext(csrv.DBINFECONNSTRING);
        }

        public void comecar()
        {
            tb_os_execucoe tex = new tb_os_execucoe()
            {
                dt_inicio = DateTime.Now,
                FK_IDos = _idos,
                FK_IDthread = _idthread,
                stts = "RUN"
            };

            db.tb_os_execucoes.InsertOnSubmit(tex);
            db.SubmitChanges();
            long idexec = tex.PK_IDexec;

            tb_thread tth = db.tb_threads.Single(t => t.PK_IDt == _idthread);
            tth.FK_IDos = _idos;
            tth.dt_inicio = tex.dt_inicio;
            tth.wait = 0;
            tth.qtd = 0;
            tth.stts = "RUN";
            db.SubmitChanges();
            

            consulta cons = new consulta(_idos, _idemp, _idthread, idexec);
            Action act;
            if (_tpps == 3)
            {
                act = new Action(cons.consultaPJ);
            }
            else
            {
                act = new Action(cons.consultaPF);
            }
            
            IAsyncResult result = act.BeginInvoke(OnActionCompleted, act);
            
            cons = null;
        }
        

        public void reComecar()
        {
            tb_os_execucoe tex = new tb_os_execucoe()
            {
                dt_inicio = DateTime.Now,
                FK_IDos = _idos,
                FK_IDthread = _idthread,
                stts = "RUN"
            };

            db.tb_os_execucoes.InsertOnSubmit(tex);
            db.SubmitChanges();
            long idexec = tex.PK_IDexec;

            tb_thread tth = db.tb_threads.Single(t => t.PK_IDt == _idthread);
            tth.FK_IDos = _idos;
            tth.dt_inicio = tex.dt_inicio;
            tth.wait = 0;
            tth.qtd = 0;
            tth.stts = "RUN";
            db.SubmitChanges();


            consulta cons = new consulta(_idos, _idemp, _idthread, idexec);
            Action act;
            if (_tpps == 3)
            {
                act = new Action(cons.reConsultaPJ);
            }
            else
            {
                act = new Action(cons.reConsultaPF);
            }
            IAsyncResult result = act.BeginInvoke(OnActionCompleted, act);
            
            cons = null;
        }

        public void finalizarArquivos ()
        {

            tb_os_execucoe tex = new tb_os_execucoe()
            {
                dt_inicio = DateTime.Now,
                FK_IDos = _idos,
                FK_IDthread = _idthread,
                stts = "RUN"
            };

            db.tb_os_execucoes.InsertOnSubmit(tex);
            db.SubmitChanges();
            long idexec = tex.PK_IDexec;

            tb_thread tth = db.tb_threads.Single(t => t.PK_IDt == _idthread);
            tth.FK_IDos = _idos;
            tth.dt_inicio = tex.dt_inicio;
            tth.wait = 0;
            tth.qtd = 0;
            tth.stts = "RUN";
            db.SubmitChanges();


            consulta cons = new consulta(_idos, _idemp, _idthread, idexec);
            Action act;
            if (_tpps == 3)
            {
                act = new Action(cons.finalizaPJ);
            }
            else
            {
                act = new Action(cons.finalizaPF);
            }
            IAsyncResult result = act.BeginInvoke(OnActionCompleted, act);

            cons = null;

        }

        private static void OnActionCompleted(IAsyncResult result)
        {
            kservDataContext db;
            fwlog log;
            configSRV csrv;
            csrv = new configSRV();
            log = new fwlog(csrv.TEMPDIR + "\\log.dbiKcad.txt", csrv.TIPOLOG);
            db = new kservDataContext(csrv.DBINFECONNSTRING);

            var action = (Action)result.AsyncState;
            action.EndInvoke(result);

            tb_os_execucoe tex = db.tb_os_execucoes.Single(t => t.PK_IDexec == ((consulta) action.Target).IDEXEC);
            tex.dt_fim = DateTime.Now;
            tex.stts = ((consulta)action.Target).ACAOTHREAD;
            
            tb_thread tth = db.tb_threads.Single(t =>t.PK_IDt == ((consulta) action.Target).IDTHREAD);
            tth.FK_IDos = 0;
            tth.qtd = 0;
            tth.wait = 0;
            tth.dt_inicio = new DateTime(1901, 1, 1);
            tth.stts = "OPN";
            
            db.SubmitChanges();
        }

       
    }

    public class controle
    { 
        kservDataContext db;
        private fwlog log;
        private configSRV csrv;
        //private Thread th;
        private tconsulta[] tcons;

        public controle()
        {
            csrv = new configSRV();
            log = new fwlog(csrv.TEMPDIR + "\\log.dbiKcad.txt", csrv.TIPOLOG);
            db = new kservDataContext(csrv.DBINFECONNSTRING);
        }

        public void rearranjaArquivos()
        {
            try
            {
                consulta cons;
                log.info("Inicio Rearranjo", "", "");
                var lth = (from tb in db.tb_threads
                           from to in db.tb_os
                           where (tb.tipo == "CON" || tb.tipo == "RCN") && tb.stts == "RUN"
                           && tb.FK_IDos == to.PK_IDos
                           && to.tpps == 3
                           && to.stts == 4
                           select new
                           {
                               IDOS = (long)to.PK_IDos,
                               IDEMP = to.FK_IDemp,
                               //TPPS = 3,
                               IDTH = tb.PK_IDt
                           }
                                       );
                foreach (var it in lth)
                {
                    log.info("reconstruir consultas.txt da OS", "OS=" + it.IDOS.ToString(), "");
                    cons = new consulta((long)it.IDOS, (int)it.IDEMP, 0, 0);
                    cons.arrumaArquivo();
                    tb_o tos = db.tb_os.Single(t => t.PK_IDos == it.IDOS);
                    tos.stts = 5;
                    db.SubmitChanges();
                    tb_thread tth = db.tb_threads.Single(t => t.PK_IDt == it.IDTH);
                    tth.stts = "OPN";
                    tth.qtd = 0;
                    tth.FK_IDos = 0;
                    db.SubmitChanges();
                    log.info("reconstrução da OS concluida", "OS=" + it.IDOS.ToString(), "");

                }
            }
            catch (Exception ex)
            {
                log.erro("controle.rearranjaarquivos", "", ex.Message + "|" + ex.StackTrace);
                throw (ex);
            }
        }

        public void rearranjaArquivosPF()
        {
            try
            {
                consulta cons;
                log.info("Inicio Rearranjo", "", "");
                var lth = (from tb in db.tb_threads
                           from to in db.tb_os
                           where (tb.tipo == "CON" || tb.tipo == "RCN") && tb.stts == "RUN"
                           && tb.FK_IDos == to.PK_IDos
                           && to.tpps == 4
                           && to.stts == 4
                           select new
                           {
                               IDOS = (long)to.PK_IDos,
                               IDEMP = to.FK_IDemp,
                               //TPPS = 3,
                               IDTH = tb.PK_IDt
                           }
                                       );
                foreach (var it in lth)
                {
                    log.info("reconstruir consultas.txt da OS", "OS=" + it.IDOS.ToString(), "");
                    cons = new consulta((long)it.IDOS, (int)it.IDEMP, 0, 0);
                    cons.arrumaArquivoPF();
                    tb_o tos = db.tb_os.Single(t => t.PK_IDos == it.IDOS);
                    tos.stts = 5;
                    db.SubmitChanges();
                    tb_thread tth = db.tb_threads.Single(t => t.PK_IDt == it.IDTH);
                    tth.stts = "OPN";
                    tth.qtd = 0;
                    tth.FK_IDos = 0;
                    db.SubmitChanges();
                    log.info("reconstrução da OS PF concluida", "OS=" + it.IDOS.ToString(), "");

                }
            }
            catch (Exception ex)
            {
                log.erro("controle.rearranjaarquivosPF", "", ex.Message + "|" + ex.StackTrace);
                throw (ex);
            }
        }

        public void iniciarServicosConsultas()
        {
            try
            {
                log.info("Inicio Consultas", "", "");
                List<tb_thread> lth = (from tb in db.tb_threads
                                       where tb.tipo == "CON" && tb.stts == "OPN"
                                       select tb).ToList<tb_thread>();
                tcons = new tconsulta[lth.Count];

                List<tb_o> los = (from tb in db.tb_os
                                  where tb.stts == 3 && tb.dt_proxExec <= DateTime.Now && tb.multipart == false
                                  select tb).ToList<tb_o>();
                if (los.Count > 0)
                {
                    for (int i = 0; i < lth.Count; i++)
                    {
                        if (i <= los.Count - 1)
                        {
                            log.info("consulta OS = ", los[i].PK_IDos.ToString(), "");
                            tcons[i] = new tconsulta(los[i].PK_IDos, (int)los[i].FK_IDemp, lth[i].PK_IDt, (int)los[i].tpps);
                            tcons[i].comecar();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                log.erro("controle.iniciarServicosConsultas", "", ex.Message + "|" + ex.StackTrace);
                throw (ex);
            }
        }


        public void iniciarServicosReConsultas()
        {
            try
            {
                log.info("Inicio REConsultas", "", "");
                List<tb_thread> lth = (from tb in db.tb_threads
                                       where tb.tipo == "RCN" && tb.stts == "OPN"
                                       select tb).ToList<tb_thread>();

                tcons = new tconsulta[lth.Count];

                List<tb_o> los = (from tb in db.tb_os
                                  where tb.stts == 5 && tb.dt_proxExec <= DateTime.Now && tb.multipart == false
                                  select tb).ToList<tb_o>();
                if (los.Count > 0)
                {
                    for (int i = 0; i < lth.Count; i++)
                    {
                        if (i <= los.Count - 1)
                        {
                            log.info("REconsulta OS = ", los[i].PK_IDos.ToString(), "");
                            tcons[i] = new tconsulta(los[i].PK_IDos, (int)los[i].FK_IDemp, lth[i].PK_IDt, (int)los[i].tpps);
                            tcons[i].reComecar();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                log.erro("controle.iniciarServicosReconsultas", "", ex.Message + "|" + ex.StackTrace);
                throw (ex);
            }
        }

        public void iniciarFinalizacaoArquivos()
        {
            try
            {
                log.info("Inicio Fechamento", "", "");
                List<tb_thread> lth = (from tb in db.tb_threads
                                       where tb.tipo == "ARQ" && tb.stts == "OPN"
                                       select tb).ToList<tb_thread>();

                tcons = new tconsulta[lth.Count];

                List<tb_o> los = (from tb in db.tb_os
                                  where tb.stts == 9 && tb.dt_proxExec <= DateTime.Now
                                  select tb).ToList<tb_o>();
                if (los.Count > 0)
                {
                    for (int i = 0; i < lth.Count; i++)
                    {
                        if (i <= los.Count - 1)
                        {
                            log.info("Fechamento OS = ", los[i].PK_IDos.ToString(), "");
                            tcons[i] = new tconsulta(los[i].PK_IDos, (int)los[i].FK_IDemp, lth[i].PK_IDt, (int)los[i].tpps);
                            tcons[i].finalizarArquivos();
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                log.erro("controle.iniciarFinalizacaoArquivos", "", ex.Message + "|" + ex.StackTrace);
                throw (ex);
            }




            //consulta cons;

            //List<tb_o> los = (from tb in db.tb_os
            //                  where tb.stts == 9
            //                  select tb).ToList<tb_o>();
            //foreach(tb_o os in los)
            //{
            //    cons = new consulta(os.PK_IDos, (int) os.FK_IDemp, 0, 0);
            //    if (os.tpps == 3)
            //    {
            //        cons.nRegistrosPara1RegistroPJ();
            //        cons.export2ExcelPJ();
            //        cons.zipArquivos();
            //    }
            //    else
            //    {
            //        cons.nRegistrosPara1RegistroPF();
            //        cons.export2ExcelPF();
            //        cons.zipArquivos();
            //    }
                
            //}
            //cons = null;
        }
    }


}


